DISTINCT / ORDER BY Questions

MSteinblock

Here is my query I am working with.

SELECT
DISTINCT Roster.RosterID AS RosterID
FROM Roster
INNER JOIN Photos ON Roster.RosterID = Photos.RosterID
INNER JOIN Users ON Photos.UserID = Users.UserID
INNER JOIN Roads ON Roads.RoadID = Roster.RoadID
ORDER BY Photos.PhotoID DESC
LIMIT 200

This query will not work right now because it is missing this:

, Photos.PhotoID AS PhotoID

When I add this to make it work, the results from the query are no longer the same. It thinks now I want the distinct values from both RosterID as well as PhotoID.

Is there a way to get a DISTINCT list of RosterID's ordered by PhotoID without putting PhotoID in the results?

Thanks for your help!!!

Lars_Berg

It thinks now I want the distinct values from both RosterID as well as PhotoID.

That's how distinct is defined. It is always applied to the complete row.

Is there a way to get a DISTINCT list of RosterID's ordered by PhotoID without putting PhotoID in the results?

That request does not make sense. As there (obviously) are multiple photoId for each rosterID, which of those possible photoId values should be used for the sorting? Most DBMS (except Mysql) will reject that type of query.

You must determine which of the possible photoId that should be used in the ordering. Give some sample data and the expected result.

MSteinblock

I will try to explain the situation a little better of what I am trying to accomplish. This is for a railroad locomotive photo database.

Each locomotive has a roster entry, telling about the locomotive. Each roster entry can have multiple photos. When a search is done, all of the photos are grouped together with the roster entry. So, I need to a list of all of the roster entries and the photos associated with them. I am trying to use DISTINCT to get the list of roster entries and be able to sort those results.

Here is the website: http://LocoPhotos.com

If anyone is interested, I would be willing to pay someone to take a look at the entire code and cleanup / fix the problem.

Thanks!

Matthew