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 its going to be easy. i just had no luck and its 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.