CODEWITHSUNDEEP
sqlsqlite
QWERTY
QWERTY

Reputation: 2315

SQL query to group by largest date

I am trying to write an SQL query to do something like for each type, find the record with the largest date. My sample data:

date             type
1505246220000    RNase P
1445652540000    Dye Plate 3
1505246940000    Dye Plate 1
1530529380000    ROI
1505246220000    ROI
1445651640000    ROI
1382579640000    ROI
1532830140000    Dye Plate 4

As you can see, for the type "ROI" it contains multiple rows. For that, I wanted to get the largest date. My current SQL query is just simply selecting everything:

SELECT * FROM calibration_history ORDER BY calibration_date DESC

Any ideas?

Upvotes: 0

Views: 42

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would use correlated subquery :

select ch.*
from calibration_history ch
where calibration_date = (select max(ch1.calibration_date) 
                          from calibration_history ch1
                          where ch1.type = ch.type
                         );

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

This is called aggregation. You don't want to "group by largest date", but by type (i.e. you want one result row per type), for which you want the largest date each:

SELECT max(calibration_date), type
FROM calibration_history
GROUP BY type
ORDER BY max(calibration_date) DESC;

(I don't know if this is the order you want or if this was your attempt to get the largest dates. Of course you can ORDER BY type instead for instance.)

Upvotes: 1

Related Questions