1

I have a mysql table with entries like this:

id  name        value   date
1   results1    1000000 2010-06-02 01:31:12
2   results2    600000  2010-09-03 05:42:54
1   results1    1200000 2010-09-06 02:14:36

How can I SELECT all and filter multiple rows that have the same id, selecting only the one with latest date?

The "date" column datatype is timestamp and it has CURRENT_TIMESTAMP as default.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
jarkam
  • 1,538
  • 5
  • 15
  • 20
  • This sounds like Slowly Changing Dimensions. You might want to review the answers to this question: http://stackoverflow.com/questions/125877/versioning-database-persisted-objects-how-would-you – Chris Vest Sep 07 '10 at 21:00

1 Answers1

1
select m.*
from (
    select id, max(date) as MaxDate
    from MyTable
    group by id
) mm
inner join MyTable m on mm.id = m.id and mm.MaxDate = m.Date
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • I can't get the query working, do I need to edit anything else besides MyTable? – jarkam Sep 08 '10 at 01:26
  • I got it working adding a few "AS", but it only retrieves one row (the one with highest date), even when other rows have different name with same id. How can I retrieve all? – jarkam Sep 08 '10 at 02:07
  • I am confused - your question says "multiple rows with same id, selecting only one with latest date", and here in the comments you seem to want something different. Can you provide example desired output? – D'Arcy Rittich Sep 08 '10 at 03:39