Get lines with maximum value in each group

Asked Aug 23 '22 at 08:36

Active Aug 23 '22 at 08:42

Viewed 14 times

I have a user table that look like this :

id	dtCreation	type
1	2022-01-01 00:00:00	aaa
2	2021-01-01 00:00:00	aaa
3	2022-01-01 00:00:00	bbb
4	2021-01-01 00:00:00	bbb
5	2021-01-01 00:00:00	ccc
6	2021-01-01 00:00:00	ccc

And I'm trying to get the last created user (by dtCreation) for each type. So far I select everything with group done on the type and selecting the line with the highest creation date in each group with the HAVING clause.

My request is :

  SELECT u.id, u.dtCreation, u.type FROM users u 
   WHERE u.type IN ('aaa','bbb','ccc')
GROUP BY u.type 
  HAVING MAX(u.dtCreation) = u.dtCreation;

But I don't get any result from this request, is there something that I'm missing ?

edited Aug 23 '22 at 08:42

asked Aug 23 '22 at 08:36

Xiidref

1,456
8
20

How do you define *last*? By id, by dtCreation, or by the combination of both? – forpas Aug 23 '22 at 08:40
Only by dtCreation – Xiidref Aug 23 '22 at 08:42
Does your query even run? Can you share a fiddle – hsnsd Aug 23 '22 at 08:42
They are the same some are from 2021 and other from 2022 (that not the real dataset but in the real one the dates are not the sames either) – Xiidref Aug 23 '22 at 08:46
Try this `SELECT id FROM tablename INNER JOIN(SELECT MAX(a.dt_creation), a.type FROM tablename a GROUP BY a.type) a ON tablename.id = a.id` – learning Aug 23 '22 at 09:34

Get lines with maximum value in each group

0 Answers0