Re: Query to extract one record from multiple records

From: <cbursell_at_geusnet.com>
Date: Tue, 11 Oct 2011 07:46:31 -0700 (PDT)
Message-ID: <39fd83de-83e3-4364-92c6-d12f79842dd5_at_k34g2000yqm.googlegroups.com>



I did a "quick and dirty" test with Joel's answer using a local copy of sqlite
I had to reverse the GROUP BY and Having so it came out like:

SELECT ITEM, DESCRIPTION, LOCATION, BIN
 FROM MTABLE
WHERE ITEM IN (SELECT ITEM
 FROM MTABLE GROUP by ITEM, LOCATION HAVING COUNT(*) > 1) AND LOCATION='WAREHOUSE'
UNION ALL
SELECT ITEM, DESCRIPTION, LOCATION, BIN
 FROM MTABLE
WHERE ITEM IN (SELECT ITEM
 FROM MTABLE GROUP by ITEM, LOCATION HAVING COUNT(*) = 1)

The above returns both results for ITEM 1234 (Only wanted the one with WAREHOUSE)
Back to the drawing board

1234	RED LAMP	WAREHOUSE	C132
1234	RED LAMP	STOREROOM	FLOOR
3456	TABLE	                    STOREROOM	X123
4231	CHAIR	                    WAREHOUSE	A432
Received on Tue Oct 11 2011 - 09:46:31 CDT

Original text of this message