Mildly Complicated Use of Min() 1

[thermidor]

Hi All:

I need to query the table below to get the Part_Nos that have the earliest Create_Dt, grouped by UPC. In cases where there is more than one matching result, I want the one with first value returned based on an alpha sort of the Part_nos. Based on the table below, I want to return:
1/4/2010 ABC 123
3/23/2009 GHI 456

My_Table
Create_Dt Part_No UPC
1/4/2010 ABC 123
2/4/2009 DEF 123
5/15/2010 XYZ 456
3/23/2009 GHI 456
3/23/2009 JKL 456

Any help would be most appreciated.

TIA, Sven

 

[RiverGuy]

DECLARE @My_Table TABLE (
Create_dt DATETIME,
Part_No VARCHAR(3),
UPC VARCHAR(3)
)


INSERT INTO @My_Table SELECT '1/4/2010',           'ABC',              '123'
INSERT INTO @My_Table SELECT '2/4/2009',           'DEF',              '123'
INSERT INTO @My_Table SELECT '5/15/2010',          'XYZ',              '456'
INSERT INTO @My_Table SELECT '3/23/2009',          'GHI',              '456'
INSERT INTO @My_Table SELECT '3/23/2009',          'JKL',              '456'    


SELECT b.*
FROM
	(SELECT UPC, MIN(Create_dt) AS MinDate
	FROM @My_Table 
	GROUP BY UPC) a
INNER JOIN @My_Table b
ON a.UPC = b.UPC AND b.Create_dt = a.MinDate
INNER JOIN 
	(SELECT UPC, Create_dt, MIN(Part_No) AS MinPart
	FROM @My_Table 
	GROUP BY UPC, Create_dt ) c
ON b.Create_dt = c.Create_dt AND b.Part_No = c.MinPart

FYI, your expected results do not match your explanation. 1/4/2010 is not the earliest date for UPC 123.

 

[thermidor]

Thanks. I was having serious brain freeze...