Find first record for each Id SQL Query

Question

I have the below table with ID and Number columns

ID Number 
1 34534 
1 45345 
1 45353 
2 56454 
2 45645 
3 65756 
3 67565 
3 87865 
3 38932 
4 36468 
4 45332 

Expected Output is

1 34534 
2 56454 
3 65756 
4 36468 

I need to take all the fist number for each Id.

How can i write a Query to achieve this? I am not expert in SQL. :(

Note: Using SQL Server 2005

Answer 1

Try something like this:

SELECT ID, Number FROM
    (SELECT ID, Number, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Number) RN FROM MyTable) Base
    WHERE Base.RN = 1

Clearly MyTable is the name of your table

Ah... Clearly 2 56454 is impossible. You can only get 2 45645. Order in an sql table is an illusion unless you use an ORDER BY clause. Otherwise the SQL server can reorder the rows howerer it wants.