MySQL Distinct question

[mksolutions]

Here is a sample 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.

The Order By part will not work until I put this part in:

Photos.PhotoID AS PhotoID

HOWEVER, when I do that, the query returns different results. How can I order by something without putting it into the query....OR how can I put that into the query yet only return the DISTINCT part?? I am stuck in a catch 22 here.

Thanks for the help!

 

[r937]

if you use DISTINCT, this forces the database to sort your result set in order to detect duplicates

after that, your ORDER BY clause is applied

but you are trying to ORDER BY something that isn't in the SELECT list, and you can only do that if you aren't using DISTINCT or GROUP BY

the problem you face now is how to describe to us what you want and why you think you need DISTINCT on it -- in other words, you need to explain the one-to-many relationships here

note also that your query has three tables in the FROM clause, yet you are referencing a fourth (Roads) in the ON conditions

r937.com | rudy.ca

 

[mksolutions]

Thanks for the reply. I understand what is going on now. Now I just do not understand how to fix it.

I think the entire search page needs rewritten as queries are taking too long. I think I have too many embedded queries.

I have given up trying to fix this myself...and have started looking for someone to take a look at the whole page to see how things can be done differently.

Thanks for the help!