Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: