query question...

[alanchinese]

suppose i have a simplied table (MyTable):

MyPrimaryKey MyYear

key1 2006

key2 2007

key1 2002

key2 2005

key1 2009

key1 2010

-----------------------------------------

i want to select from this table, with 'MyYear' to be the minimum year grouping by the 'MyPrimaryKey'.

so the result set should be:

MyPrimaryKey MyYear

key1 2002

key2 2005

key1 2002

key2 2005

key1 2002

key1 2002

-----------------------------------------

i am sure it's going to be easy. i just had no luck and it's hurry.

thanks for your help.

[Gill Bates]

Option 1:

SELECT MyPrimaryKey, (SELECT MIN(MyYear) FROM MyTable I WHERE I.MyPrimaryKey = O.MyPrimaryKey)
 FROM MyTable O

Option 2:

SELECT O.MyPrimaryKey, MinYears.MyYear
 FROM MyTable O
INNER JOIN (SELECT MyPrimaryKey, MIN(MyYear) MyYear FROM MyTable GROUP BY MyPrimaryKey) As MinYears ON MinYears.MyPrimaryKey=O.MyPrimaryKey

Option 3:

CREATE TABLE #min
(
MyPrimaryKey varchar(5),
MyYear int
)

INSERT INTO #min
 SELECT MyPrimaryKey, MIN(MyYear) 
   FROM MyTable 
GROUP BY MyPrimaryKey

SELECT O.MyPrimaryKey, M.MyYear
 FROM MyTable O
INNER JOIN #min M ON M.MyPrimaryKey=O.MyPrimaryKey

DROP TABLE #min

Your choice should depend on how much data you have. The first query should work well for a modest amount of data. Otherwise I would use the second or the third. Both should be able the same in terms of efficiency.