Return first row only for each of the same group

Question

How would I just display the first record the query comes to for each set of groupings in a certain column.  Say I have some names with phone numbers and I have sorted these so that even though one person might have 3 phone numbers, the most relevant one is going to be the first one listed.  I can get my query sorted where I can do it this way, but I can't seem to find a way to return the first record of a group like in the example above.

Answer 1

If you are using 2005, you can do this with a derived table and the row_number function:

select *
from   (
        select *,
                row_number() over (partition by orderId order by orderId ) as rowNum
        from   northwind.dbo.[order details] ) as od
where rowNum = 1

 

Answer 2

Ah, I saw this ROW_NUMBER() in the online MSDN T-SQL language documentation but it read like greek when trying to figure out what was going on and how to actually use it.  Could you explain?

Answer 3

It simply returns the position of a row in the output set.  It is easy enough:

row_number() over (partition by columnName order by columnName )

Partition by (optional) is used to reset the count:

Partition by id

Id        row_number
1         1
1         2
1         3
2         1
3         1

Order by is used to specify the order that the row_number value will be assigned, so if I had said:

order by id desc --without a partition clause:

Id        row_number
1         5
1         4
1         3
2         2
3         1

Note that the row_number has nothing to do with the order of the output from the order by clause, for some decent example code, check out the sample code from the chapter I wrote in Pro SQL Server 2005 (buying the whole book would be appreciated, but there are other good books too :) in chapter 3: http://www.apress.com/book/supplementDownload.html?bID=457&sID=3206.  I would just play with it a bit, it is pretty straightforward once you get the hang of it.