achieve "order by" before "group by" in mysql-CodePudding

i have a DB with this structure

id, UserID, price, Date

,1, 10.00, 2000-01-01
,1, 25.00 ,2022-02-01
,2, 12.00 ,2000-01-05
,2, 13.00 ,2001-01-05
,2, 24.00 ,2022-01-01
,3, 23.00 ,2022-01-01

i want to show the price for each user based on newest date. So just 1 row per UserID (latest date)

if we query above table. results need to be like this:

    ,1, 25.00 ,2022-02-01
    ,2, 24.00 ,2022-01-01
    ,3, 23.00 ,2022-01-01

i have tried those 2 commands but they are not working

SELECT UserID,price,Datee FROM (SELECT UserID,price,Datee FROM tbl ORDER BY UserID ASC,datee DESC) as tb_temp GROUP BY UserID

also this

SELECT UserID,price,max(Datee) FROM tbl Group by UserID ORDER BY UserID ASC,datee DESC

this command show latest date but price is not based on that

so i need something like ORDER BY datee then group by userID or LIMIT 1 per userID

CodePudding user response：

If you're on MySQL 8, the most common way to achieve this is with a window function:

select userid, price, date
  from (select *
             , row_number() over (partition by userid 
                                      order by date desc) as row_priority
          from tbl
     ) subq
 where subq.row_priority = 1

Here's a working demo on dbfiddle

CodePudding user response：

You could use a window function to good effect as MarcinJ has done--another approach would be to use a correlated subquery:

SELECT
  S1.UserID,
  S1.price,
  S1.Date
FROM
  SampleDetails S1
WHERE
  S1.Date = (SELECT MAX(S2.Date) FROM SampleDetails S2 WHERE S2.UserID = S1.UserID GROUP BY S2.UserID);

You can try it out on DB Fiddle.

CodePudding user response：

You could try this, it's not the most efficient because it uses a sub-query:

SELECT t.UserID, t.price, t.Datee
FROM tbl t
JOIN tbl on t.id = (SELECT id FROM tbl WHERE UserID = t.UserID ORDER BY Datee DESC LIMIT 1)
GROUP BY UserID

The idea is to join the table to itself by finding the latest row for the user id.

I made this sql fiddle to test it: http://sqlfiddle.com/#!9/63d495/2