Jump to content
Marc

MSSQL Row number column

Recommended Posts

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 :)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...