Q
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?

This was last published in April 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close