Page 1 of 1

mysql select order by question

Posted: Fri Dec 03, 2004 4:21 am
by bytte
I have a table that contains bandnames, an ID and a URL.
It is perfect possible that a bandname appears twice or more.

Somewhere on my website I have a drop down list that displays the bandnames, alphabetically, grouped by bandname.
I want the option value to be the latest ID (highest number) that corresponds with the bandname.

I thought this would have been the solution, but it appears not to work:

SELECT ID, bandname, URL from bands GROUP BY bandname ORDER BY bandname ASC, ID DESC

Posted: Fri Dec 03, 2004 4:42 am
by cggreer
I'm not too sure exactly what data you're trying to retrieve.

Assuming that you just want to list all URLs for the selected bandname, this query will work:

Code: Select all

SELECT URL
FROM bands
WHERE bandname = $selected_band
If you were trying to populate the listbox with bandnames and only wanted each one to appear once you could use:

Code: Select all

SELECT DISTINCT bandname
FROM bands
ORDER BY bandname DESC
If you want to list each bandname only once, and have the corresponding OPTION value equal the highest ID for that bandname... possibly reconsider the design. Why do you need the highest ID value for that bandname?

Posted: Fri Dec 03, 2004 9:52 am
by pickle
Your query won't work because the group by will squish all entries down until you just get 1 per band. That entry may not necessarily contain the highest ID.

I'm not sure you can do this in 1 query. I think you have to do a query to get all distinct bandnames first, then do a query to get the max(ID) for each bandname.

Posted: Sat Dec 04, 2004 6:40 am
by bytte
allright. thanks. the distinct thing however, appears not to work.

Posted: Sat Dec 04, 2004 7:03 am
by timvw
SELECT MAX(ID) AS ID, bandname
FROM bands
GROUP BY bandname
ORDER BY bandname ASC