Get previous from maximum value
I have an sql query that wants to get the previous max value from a table.
select max(card_no),vehicle_number
FROM WBG.WBG_01_01
group by vehicle_number
Through this request, I have received each maximum card number of each vehicle. But I want to get the previs of this max. for example
if the car number has a card number 21,19,17,10,5,6,1
and I want to get 19
from the max
function
Please tell me how can I do this in sql.
+3
source to share
2 answers
Another idea is to use analytics, something like this:
select
vehicle_number,
prev_card_no
from (
select
card_no,
vehicle_number,
lag(card_no) over
(partition by vehicle_number order by card_no) as prev_card_no,
max(card_no) over
(partition by vehicle_number) as max_card_no
FROM WBG.WBG_01_01
)
where max_card_no = card_no;
Of course, this doesn't account for your seeming arbitrary order from your question, and won't work with duplicate maximum numbers.
+1
source to share