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
mysql select order by question
Moderator: General Moderators
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:
If you were trying to populate the listbox with bandnames and only wanted each one to appear once you could use:
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?
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_bandCode: Select all
SELECT DISTINCT bandname
FROM bands
ORDER BY bandname DESCYour 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.
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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.