Re: Query to extract one record from multiple records

There will always be at least one location which will be either Warehouse or Storeroom. However, there are occasions where the same item is located in both locations. IN that event we wish to return the record with the Warehouse location and ignore the one with the Storeroom location. In the case of only one location, return that record.

I realize this is a very simplified example for a more complex query. I do not understand how the table creation and data loading commands will help. Am I missing something?

With those conditions in mind a simple grouping and a MAX function on the LOCATION field would always ensure that WAREHOUSE would be selected over STOREROOM, and thus two possible simple solutions comes to (my) mind.

SELECT ITEM, DESCRIPTION, LOCATION, BIN
FROM <TABLE> T1
WHERE LOCATION = (SELECT MAX(LOCATION) FROM <TABLE> WHERE ITEM=T1.ITEM AND DESCRIPTION=T1.DESCRIPTION) OR

SELECT T1.ITEM, T1.DESCRIPTION, T1.LOCATION, T1.BIN FROM <TABLE> T1
INNER JOIN (
    SELECT ITEM, DESCRIPTION, MAX(LOCATION) AS LOCATION     FROM <TABLE>
   GROUP BY ITEM, DESCRIPTION
) T2 ON T1.ITEM=T2.ITEM AND T1.DESCRIPTION=T2.DESCRIPTION AND T1.LOCATION=T2.LOCATION Received on Sun Nov 13 2011 - 15:04:20 CST