Selecting the minimum from a query with a group
I have the following request:
SELECT a.topicID, d.catalogFileID, d.catalogFileExtension, a.sortorder
FROM catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID
WHERE b.fileTypeID = 'gvl401'
AND c.fileTypeID = 'gvl25'
AND a.topicid = 'top340'
which chooses:
topicID catalogFileID catalogFileExtension sortorder
top340 cfil1070 jpg 5
top340 cfil958 jpg 7
top340 cfil958 jpg 7
top340 cfil956 jpg 6
top340 cfil864 jpg 4
top340 cfil862 jpg 3
but instead of returning multiple rows, I just want to get one row (minimum sort order). So in this case the last line.
I tried the following query:
SELECT a.topicID, d.catalogFileID, d.catalogFileExtension, min( a.sortorder )
AS smallestorder
FROM catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID
WHERE b.fileTypeID = 'gvl401'
AND c.fileTypeID = 'gvl25'
AND a.topicid = 'top340'
GROUP BY a.topicid
which chooses:
topicID catalogFileID catalogFileExtension smallestorder
top340 cfil1070 jpg 3
It is not right. Even though I got the smallest sort order ... I didn't get a row that matches this.
If it's easier to solve by storing the results of the first query in a separate table, I'm open to that as well.
+2
josh
source
to share
2 answers
You can use ORDER BY and LIMIT instead of GROUP BY:
SELECT a.topicID, d.catalogFileID, d.catalogFileExtension, a.sortorder
FROM catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID
WHERE b.fileTypeID = 'gvl401'
AND c.fileTypeID = 'gvl25'
AND a.topicid = 'top340'
ORDER BY a.sortorder ASC
LIMIT 1
+1
source to share