|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
May 31st, 2008, 11:04 PM
|
|||
|
|||
|
How To: get records using max date
I have never had to do this before, I have looked but have not been able to find a solution.
I have a table with the following fields: ID | UpdateDt | Val 1 2008-01-01 1 1 2008-01-02 4 1 2008-01-10 7 2 2008-01-01 3 2 2008-01-05 5 3 2008-01-01 7 3 2008-01-06 4 I want to retrieve the row for each id with the max date 1 2008-01-10 7 2 2008-01-05 5 3 2008-01-06 4 I have tried various ways, the closest I got was to get the max date, but the first row for each id.
|
|
#2
June 2nd, 2008, 11:06 AM
|
||||||
|
||||||
|
OK well getting the val field makes this a bit tricky. I haven't entered your data to make sure this will work, but it should.
Code:
SELECT <tbl>.ID, <tbl>.UpdateDt, <tbl>.Val FROM <tbl> INNER JOIN (SELECT <tbl>.ID, Max(<tbl>.UpdateDt) as MaxDate FROM <tbl> GROUP BY ID) as TMax ON <tbl>.ID = TMax.ID AND <tbl>.UpdateDt = TMax.MaxDate ORDER BY <tbl>.ID Enjoy!
|
|
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > How To: get records using max date |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
