Jump to content


Photo

Nesting queries


  • Please log in to reply
3 replies to this topic

#1 llama

llama
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationLeicester, UK

Posted 10 November 2003 - 06:58 PM

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.
www.subhex.co.uk
[br]

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 11 November 2003 - 08:25 PM

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?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 llama

llama
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationLeicester, UK

Posted 11 November 2003 - 08:27 PM

The latest

By default its taking the 1st one it encounters, usually the earliest.
www.subhex.co.uk
[br]

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 11 November 2003 - 08:29 PM

SELECT id, MAX(thedate) as latestdate FROM tablename
GROUP BY id
ORDER BY latestdate
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users