selecting minimum from a query with group by

Question

I have the following query:

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 fetches:

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 getting back multiple rows I just want to get one row (the minimum sortorder). So in this case the last row.

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 fetches:

topicID  catalogFileID  catalogFileExtension  smallestorder
top340  cfil1070  jpg  3

That's wrong. Even though I got the smallest sort order... I did not get the row corresponding to that.

If it is easier to solve this by storing results of first in a separate table then I'm open to that as well. I'm doing this in MySQL.

Answer 1

You could 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

Answer 2

SELECT ... ORDER BY a.sortorder LIMIT 1