MS SQL MAX QUERY, how do I return "multiple" MAX values in one query?
Posted on 2013-06-18
I have two tables. One contains a big pile of product information as well as the page number that the item appears in, in a catalog. The second table contains the imagePath for the items, but each item has two images associated with it. One is low res, the other high res. Using the supplier code, how do I return only the high res imagePaths for each code? I feel that MAX is the right path to proceed down, but I'm probably wrong.
SELECT MAX(tblImagePaths.fldItemSize) AS LargeImage, tblSupplierData.fldPage, tblImagePaths.fldItemCode
INNER JOIN tblImagePaths ON tblSupplierData.fldItemCode = tblImagePaths.fldItemCode
WHERE (tblImagePaths.fldItemCode, 5) = @monthYear) AND (tblSupplierData.fldPage = @pageNumber)
GROUP BY tblImagePaths.fldItemSize, tblSupplierData.fldPage, tblImagePaths.fldItemCode