Problem solve Get help with specific problems with your technologies, process and projects.

Recalculating row rank

I have a table like

id(auto)   name    rank
  1        ABCD     3
  2        EFGH     2
  3        IJKL     1

Rank is the display order of the name. If I want to change the display order, I want to interchange the ranks. How can I interchange the rank using a single SQL query, or how can I optimize the following queries:

  1. select rank from table where id=(given id)
  2. select top 1 id,rank from table where id>(given id)
  3. update table set rank=(rank from first result) where id=(id from second result)
  4. update table set rank=(rank from the second result and the given id)

My advice in these scenarios, where you have a column whose purpose is to establish a sort sequence, is to use values that allow plenty of space "in between" existing values.

For example, use values that increase by 100:

id(auto)   name     seq
  1        ABCD     300
  2        EFGH     200
  3        IJKL     100

Notice how I've named the column seq, to more clearly distinguish it from a rank. Then, if you wanted to add a 4th row "in between" ABCD and EFGH, you would assign it a seq value of 250:

id(auto)   name     seq
  1        ABCD     300
  2        EFGH     200
  3        IJKL     100
  4        PQRS     250

This makes inserting new rows into the table significantly more efficient than renumbering, because you would need, on average, to renumber fully half of all the rows in the table on any insertion within the existing rank numbers.

Yes, eventually you may have inserted so many numbers in between two existing numbers that you actually have to do some renumbering, but the rows which require renumbering are "local" to the area of congestion. Only a small number of rows need to be renumbered, the "neighbouring" rows above and below the congestion.

So that takes care of your main sequencing issues. If you actually still need a rank for a given id (your first question), you can get this through the query easily:

select name
     , ( select count(*)+1
           from yourtable
          where seq < X.seq ) as rank
  from yourtable as X
 where id = 937

Count the number of rows that have a lower sequence number, add 1, and that's the rank of the given row. Neat, eh?

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.