Nesting queries

[llama]

I\'m look for any sort of advice as I don\'t really know how to attack this.

 

My basic problem is that I need to SELECT from my table but have it GROUP BY it\'s \"ID\" (many records can have the same id) I then need to display these in date order.

 

When I group by it takes the date as being the date of the 1st record it encounters (this is normally the oldest date for that \"ID\"). So when I then ORDER BY all it does is gives me the order the 1st records with a particular id were created on, obviously not what I want.

 

I\'m thinking to do something such as:

 

SELECT ........... ORDER BY date

 

but then I would need to run another query on the results of the 1st query:

 

SELECT .......... GROUP BY id

 

Any ideas would be appreciated and I\'ll try them out.

[Barand]

If you group by ID you get 1 row per id. If the records with the same id have different dates, which date do you want - earliest, latest, average?

[llama]

The latest

 

By default its taking the 1st one it encounters, usually the earliest.

[Barand]

SELECT id, MAX(thedate) as latestdate FROM tablename

GROUP BY id

ORDER BY latestdate