mysql select order by question

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
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

mysql select order by question

Post 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
cggreer
Forum Newbie
Posts: 14
Joined: Mon Jul 21, 2003 6:39 am

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Post by bytte »

allright. thanks. the distinct thing however, appears not to work.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

SELECT MAX(ID) AS ID, bandname
FROM bands
GROUP BY bandname
ORDER BY bandname ASC
Post Reply