I suspect mysql is doing the DISTINCT before the ORDER BY. One of the
reasons I avoid DISTINCT when possible. Try:
SELECT Documents.ID, Name
FROM Documents, DocumentFiles, DownloadLog
WHERE Documents.ID = DocumentFiles.Document
AND DocumentFiles.ID = DownloadLog.DocumentFile
GROUP BY Documents.ID, Name
ORDER BY MAX(DownloadLog.AddedOn) DESC LIMIT 5;
Michael
Gustafson, Tim wrote:
Hello
I am trying to run the following query on my server:
select Documents.ID,
Name
from Documents,
DocumentFiles,
DownloadLog
where Documents.ID = DocumentFiles.Document and
DocumentFiles.ID = DownloadLog.DocumentFile
order by DownloadLog.AddedOn desc
limit 5
It works correctly, except that it displays duplicate rows. If I put a
"distinct" before "Documents.ID", it gives me only unique rows, but the
rows are not ordered correctly. For example, without the "distinct"
clause, I get the following rows:
ID Name
10 Test 1
10 Test 1
11 Test 2
10 Test 1
12 Test 3
With the "distinct" clause, I get this:
ID Name
12 Test 3
13 Test 4
10 Test 1
11 Test 2
14 Test 5
Why does adding the "distinct" clause change the order of the rows
completely?
Thanks in advance!
Tim Gustafson
MEI Technology Consulting, Inc
[EMAIL PROTECTED]
(516) 379-0001 Office
(516) 480-1870 Mobile/Emergencies
(516) 908-4185 Fax
http://www.meitech.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
