Page 1 of 1

GROUP BY and ORDER BY

Posted: Sat Jul 31, 2004 11:29 pm
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...

Posted: Sat Jul 31, 2004 11:34 pm
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.

Posted: Sat Jul 31, 2004 11:37 pm
by Illusionist
hmm... Want to try and explain how that could possibly help, or have anything to do with what I'm asking?

Posted: Sat Jul 31, 2004 11:42 pm
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