T-SQL, Select first row of a set

Question

I have a table that is setup like so:

ID        int
Hash      varchar
OtherID   int

Some Example 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 out just the first row of each hash set:

1   3pm6Qav1Vd   23
5   RSUBe2VBtl   2

The rows would each be the lowest ID for each Hash. I'm using T-SQL on SQL Server 2005. I'm not really sure where to begin on this one.

Answer 1

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

Answer 2

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