How can you get the record number in a select statement? For example, I want to do something like this --
select recordnumber, * from table
so that the result returns a numbered list.
Short answer: apply the row number to the rows in the result set in your application program. This provides the flexibility to change the sequence of the query, or the fields it's selecting on, with no further intervention.
Longer answer: you can do it in SQL, but it isn't terribly efficient. It's called a ranking query.
Here's a sample table:
PERSONS fname lname age IQ mary smith 24 109 jane doe 17 123 bill adams 32 111 fred brown 28 132 tom white 31 125
To start, let's just list these people by increasing age (youngest first) --
select fname, lname, age, IQ from persons order by age
fname lname age IQ jane doe 17 123 mary smith 24 109 fred brown 28 132 tom white 31 125 bill adams 32 111
Now here's the ranking query by increasing age --
select (select count(*) from persons) - count(*) + 1 as rank , t1.fname, t1.lname, t1.age, t1.IQ from persons as t1 , persons as t2 where t1.age <= t2.age group by t1.fname, t1.lname, t1.age, t1.IQ order by 1
rank fname lname age IQ 1 jane doe 17 123 2 mary smith 24 109 3 fred brown 28 132 4 tom white 31 125 5 bill adams 32 111
How does the ranking query work? For every row, it counts all the rows that have an equal or greater age, and subtracts the result from the number of rows in the table plus 1, which for our example table is 5 + 1 = 6. In the case of Mary Smith, there are 4 rows that have an equal or greater age (including Mary's row itself), so the rank is 2.
Let's try another example. Let's rank them by decreasing IQ (smartest first) --
select (select count(*) from persons) - count(*) + 1 as rank , t1.fname, t1.lname, t1.age, t1.IQ from persons as t1 , persons as t2 where t1.IQ >= t2.IQ group by t1.fname, t1.lname, t1.age, t1.IQ order by 1
rank fname lname age IQ 1 fred brown 28 132 2 tom white 31 125 3 jane doe 17 123 4 bill adams 32 111 5 mary smith 24 109
Notice that this time, the join condition is reversed, because we're after the rank of a descending number. However, the ORDER BY is still on rank, increasing.
This type of join is called a theta join (joining a variable number of rows based on an inequality). As you can imagine, the database has to have a good optimizer for it to perform efficiently for large tables. Hence the short answer: do it in your application program if you can. It's simpler, too, because you don't have to keep re-thinking which way the inequality is supposed to go.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.