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

Row number in a SELECT statement

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.

This was last published in May 2002

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