Table like this:
date | id | value
2003-10-01 | A | 100
2003-10-02 | A | 300
2003-10-03 | A | 200
2003-10-01 | B | 300
2003-10-02 | B | 100
2003-10-02 | C | 200

Should return result like this:
date | id | value
2003-10-03 | A | 200
2003-10-02 | B | 100
2003-10-02 | C | 200

Point is to select the newest record (by date) for each of the id (only one for each id)

SELECT DISTINCT... will return all records.

SELECT ... ORDER BY date DESC LIMIT 1 will return just 1 record.

SELECT MAX(date), id, value FROM table GROUP BY id
will return unpredictable (some times correct) result.

Any Solutions?

Thanks

Don't know much about MySQL, but your syntax

SELECT MAX(date), id, value FROM table GROUP BY id

would be invalid in MSSQL and Oracle. The column "value" should be included in the GROUP BY expression, or removed from the select list.

Regards.

CVM.

The below should work so long as the value is always three in length, or you could rewrite this otherwise.

select id,
max(date) as date,
right( max( concat(date, value)) ,3) as value
from test
group by id;


http://www.mysql.com/doc/en/example-...group-row.html