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:
- select rank from table where id=(given id)
- select top 1 id,rank from table where id>(given id)
- update table set rank=(rank from first result) where id=(id from second result)
- 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
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.