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

Question

question

Fidell asked Apr 2, '11

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-2005 t-sql ranking

10 |1200

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

Answer 1 · 2011-04-02T07:32:50Z

HelloFOFO answered Apr 2, '11

Maybe, this will help: SELECT *,RANK() OVER(ORDER BY ScoresTotal DESC) AS Rnk FROM yourtable

1

10 |1200

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

Oleg commented · Apr 02, 2011 at 07:15 PM

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 ·

question