CODEWITHSUNDEEP
mysqlsql
user1650487
user1650487

Reputation: 117

MySQL Select Latest for multiple values

enter image description here

I have this database and want to query the latest dates for only the ID's 5,10,15,20. The result of this query should be only the first four rows of that database.

How would I do that?

Upvotes: 1

Views: 1313

Answers (4)

SQB
SQB

Reputation: 4078

If it's just the ID and the corresponding date you need, the following should suffice:

SELECT
   mt.myID,
   MAX(mt.date) AS latestDate
FROM myTable mt
WHERE mt.myID IN (5, 10, 15, 20)
GROUP BY myID

Upvotes: 0

S.K. Venkat
S.K. Venkat

Reputation: 1807

Not an optimal solution, but its working :

select myid, mydate from myTable where myid in (5,10,15,20) order By mydate DSC limit 4;

Upvotes: 0

Payne Miller
Payne Miller

Reputation: 189

You could use a correlated subquery to find the max dates for the listed id's. https://en.wikipedia.org/wiki/Correlated_subquery

`SELECT * 
FROM Foo f1
WHERE f1.Date = (
                 SELECT MAX(Date)
                 FROM Foo f2
                 WHERE f1.Id = f2.Id
                )
AND f1.Id IN (5,10,15,20);`

Without having your exact table structure locally, I can't verify the syntax, but this is the general idea.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

You can use in and group by

 select * 
 from my_table 
 where (date, myID) in (select max(date), myID 
                       from my_table 
                       where myID in  (5,10,15,20)
                       group by myID );

Upvotes: 3

Related Questions