question

Fidell avatar image
Fidell asked

Query a database to give a column(Position) for Ordinal Positions

I have a table having columns- *Id*, *Name*, *Sex* and *ScoresTotal*. I want to query it to insert a column *Position* whereby records 1st, 2nd, 3rd, 4th etc will automatically inserted based on *ScoresTotal*. That is, 1st should be inserted in the *Position* column for the the row with the highest *ScoresTotal*. 2nd for the next, etc. Please help. Thanks
sql-server-2005t-sqlranking
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
HelloFOFO avatar image
HelloFOFO answered
Maybe, this will help: SELECT *,RANK() OVER(ORDER BY ScoresTotal DESC) AS Rnk FROM yourtable
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Using rank() will allow holes in the numbers. For example, if there are 2 records with the same highest score then rank will assign number 1 to both, but the next score will get number 3. If this is not desired then **dense\_rank()** could be used in place of rank(). This way both higest scores will get to be ranked 1 and the next will be ranked 2. If the holes in Position are acceptible then using the script in the answer does the trick.
4 Likes 4 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.