Jump to content

Archived

This topic is now archived and is closed to further replies.

llama

Nesting queries

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

The latest

 

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

Share this post


Link to post
Share on other sites

SELECT id, MAX(thedate) as latestdate FROM tablename

GROUP BY id

ORDER BY latestdate

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.