Q

Referencing rows by row number

I need to travel sequentially through a SQL table. The program is outside of SQL and processes it by passing SQL statements through it's interface. Is there a way to do a SELECT statement that would reference the row number? For example, if there are 7 records, is there a way to do a select statement to access record number 2.

No, not without making reference to some sequence field.

Unless you specifically design a "row number" or other sequence field into the table, the rows have no inherent sequence. Think of the table as a drawer full of socks. You cannot say which one is the "2nd sock" because each time you do the experiment, you get a different sock. Now, if you were to tag each sock with a "sock number" then I suppose you could ask for the sock with the second lowest sock number.

So if there is a field that you can sequence the records on, you can ask for record number 2. Basically, you need to ask for the record with the lowest key that is higher than the lowest overall key. The query looks like this --

   select sockdata
     from yourDrawer
    where socknumber = 
          ( select min(socknumber)
              from yourDrawer
             where socknumber >
                   (select min(socknumber)
                      from yourDrawer)
          )

If you are using MySQL, there's an even easier way to do this--

   select sockdata
     from yourDrawer
 order by socknumber 
    limit 1,1

When two numbers are given in MySQL's LIMIT clause, the first is the offset (where the first record has offset 0), and the second is the number of rows to return. So LIMIT 1,1 returns the second row. Note you have to include the ORDER BY clause, otherwise you may get a different second sock every time.

For More Information

  • What do you think about this answer? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have a SQL tip to offer your fellow DBA's 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.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in November 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close