Thursday, January 11, 2007 8:31 PM
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.
Thursday, January 11, 2007 9:04 PMModerator
If you are using 2005, you can do this with a derived table and the row_number function:
row_number() over (partition by orderId order by orderId ) as rowNum
from northwind.dbo.[order details] ) as od
where rowNum = 1
Thursday, January 11, 2007 9:18 PMAh, 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?
Tuesday, January 16, 2007 5:19 AMModerator
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
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:
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.