GROUP BY and ORDER BY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

GROUP BY and ORDER BY

Post by Illusionist »

I've got a table similar to:

id | name
1 name1
2 name2
3 name5
4 name5
5 name3
6 name1
7 name1


And what I'm trying to do is gruop by the name but order by the id descending. But it is using the first occurence of the id to order by, and i want it to use the last occurence.

my query : SELECT * FROM visitors GROUP BY name ORDER BY id DESC

How can i get it to sort in descending order by the id on the last occurence of the id??

If that makes any sense...
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Note that if you are using MySQL 3.22 (or earlier) or if you are trying to follow standard SQL, you can't use expressions in GROUP BY or ORDER BY clauses. You can work around this limitation by using an alias for the expression:
This may help.
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

hmm... Want to try and explain how that could possibly help, or have anything to do with what I'm asking?
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

nvm, I got it! I re-read it and understood enough to get this working

Code: Select all

SELECT *,MAX(id) as mid FROM visitors GROUP BY name ORDER BY mid
Thanks
Post Reply