mssql Row number column

[Marc]

Thought I would post up a bit of an example on row numbers within MSSQL. This is something that can come in handy in a multitude of senarios and there are a couple of handy things you can do with in sql server.

 

 

Lets say I have a dataset consisting of just name, value and I want the top 10 with the highest value first along with their rank. This is somewhere that adding a row number would be quite handy.

 

So do this you would use the following:

 

SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY value DESC) as RANKING,
[name],[value]

 

This will give me a row number next to each result line when the dataset is ordered by value descending. Top 10 added to give me the top 10 results

 

 

Now lets go further than this and say we have another column known as dept. This time I want the order of value, but I want it for each department. So sales for example would have 1-x, as would accounts.

 

 

SELECT TOP 10 ROW_NUMBER() OVER(PARTITION BY dept ORDER BY value DESC) as RANKING,
dept, [name],[value]

 

This will order by dept then by value. The row number will be reset for each dept and therefore giving me a ranking for each department