select the max value of 2 variables

Question

I have the situation where I need to get the record of the latest date and then the max SEQ number. Example:

CLIENT_ID  STATUS  DATE        SEQ
 10          1     2019-01-03   1
 10          2     2019-01-03   2
 10          4     2019-01-02   3
 10          4     2019-01-01   2

Expected result

CLIENT_ID  STATUS  DATE        SEQ
 10          2     2019-01-03   2

I have tried the following code however I am having problem to filter the SEQ variable a to get the max value

SELECT client_ID,status,maxdate 
FROM sitcli 
    INNER JOIN (SELECT MAX(date) maxdate 
                FROM sitcli GROUP BY 1) a 
       ON sitcli.client_id=a.client_id AND a.maxdate=sitcli.date;

Appreciate any help

score 3 · Accepted Answer · answered Jan 15 '20 at 19:07

3

You can use select first:

select first 1 t.*
from t
order by date desc, seq desc

answered Jan 15 '20 at 19:07

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, simple solution and it worked. Thank you. – Jonathan Livingston Seagull Jan 15 '20 at 21:32
1

if this query runs often you better create a descending index by those columns, or at least by date. – Arioch 'The Jan 16 '20 at 09:33

select the max value of 2 variables

1 Answers1