Geek Answers Handbook

T-SQL, select the first row of the set

I have a table that is configured like this:

ID int Hash varchar OtherID int

Some sample data:

 1 3pm6Qav1Vd 23 2 3pm6Qav1Vd 2 3 3pm6Qav1Vd 19 4 3pm6Qav1Vd 17 5 RSUBe2VBtl 2 6 3pm6Qav1Vd 4 7 3pm6Qav1Vd 21 8 RSUBe2VBtl 23 9 RSUBe2VBtl 19

I would like to be able to pull only the first line of each hash set:

 1 3pm6Qav1Vd 23 5 RSUBe2VBtl 2

Each line will be the smallest identifier for each hash. I am using T-SQL on SQL Server 2005. I am not sure where to start from this.

+4
source share
4 answers
 SELECT t.ID, t.Hash, t.OtherID FROM (SELECT ID, Hash, OtherID, ROW_NUMBER() OVER(PARTITION BY Hash ORDER BY ID) AS RowNum FROM YourTable) t WHERE t.RowNum = 1
+7
source

Simple!

 SELECT * FROM [tableName] WHERE ID IN ( SELECT MIN(ID) FROM [tableName] GROUP BY Hash )

Hope this helps.

+3
source 
 select ID, Hash, OtherId from ( select ID, Hash, OtherId, row_number() over (partition by Hash order by ID) as RN from yourtable ) a where RN = 1
+3
source

Do something like below

  SELECT * FROM Table T1 INNER JOIN ( SELECT MIN(ID) ID FROM Table GROUP BY Hash) T2 ON T1. ID = T2.ID

Hope this helps!

+1
source

More articles:


All Articles