SQL max by two columns

Question

I want to get the lastest order by order_date and detail

Here is my query and the result:

select distinct
ORDER.user_id,
order_amount,
detail,
order_date

From User innner join ORDER on User.user_id = ORDER.user_id

where order_date > '1/1/2011'
and ORDER.user_id = '2123'

user_id    order_amount    detail     order_date
2123       5               case       2012-04-07 00:00:00.000
2123       4               pack       2019-11-16 00:00:00.000
2123       2               pack       2018-12-23 00:00:00.000
2123       1               case       2019-05-25 00:00:00.000
2123       6               case       2018-04-15 00:00:00.000

Below is what I desire:

user_id    order_amount    detail     order_date
2123       4               pack       2019-11-16 00:00:00.000
2123       1               case       2019-05-25 00:00:00.000

Any help would be greatly appreciated.

You forgot to include your code you wrote that produced the wrong output. — dfundako, Jul 23 '19 at 14:53
Could you please provide the code you've tried so far along with details of what is not working as desired. — HereGoes, Jul 23 '19 at 14:54
Checkout this: https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — fiveelements, Jul 23 '19 at 15:06

forpas · Answer 1 · 2019-07-23T15:24:23.613

With NOT EXISTS:

select t.* from tablename t
where 
  t.order_date > '2011-01-01' 
  and 
  t.user_id = '2123'
  and 
  not exists (
    select 1 from tablename
    where user_id = t.user_id and detail = t.detail and order_date > t.order_date
  )

See the demo.
Results:

> user_id | order_amount | detail | order_date         
> ------: | -----------: | :----- | :------------------
>    2123 |            4 | pack   | 16/11/2019 00:00:00
>    2123 |            1 | case   | 25/05/2019 00:00:00

SQL max by two columns

1 Answers1