Ask the Expert

Show the first three records of my table

I need to show the first three records of my table in different places of the page. How do I write the SQL to show the first, second, and third record? They are ordered by CODIGO, descending.

    Requires Free Membership to View

If you had asked much beyond three, I think I would have resorted to using the ranking query for a generic number. As three levels of subquery are still reasonable efficient, let's just apply brute force.

The first record is easy --

 
select * 
  from mytable 
 where CODIGO = 
       ( select max(CODIGO) 
           from mytable )

The second one is the highest one that isn't the first --

 
select * 
  from mytable 
 where CODIGO = 
       ( select max(CODIGO) 
           from mytable 
          where CODIGO < 
                ( select max(CODIGO) 
                    from mytable )  )

The third one is the highest one that isn't the highest one that isn't the first one --

 
select * 
  from mytable 
 where CODIGO = 
       ( select max(CODIGO) 
           from mytable 
          where CODIGO < 
                ( select max(CODIGO) 
                    from mytable 
                   where CODIGO < 
                        ( select max(CODIGO) 
                          from mytable )  )  )

Like I said, I wouldn't want to go much deeper than three this way.

You said you wanted to show these three records "in different places of the page" so you would probably not want to use three separate queries like the ones above. It sounds like you are formatting the output with some kind of page-building software (e.g. report writer) or script (e.g. ASP, PHP, CF), so you should really look into executing just one query which will return all the rows you need in just one call to the database. Try to find out whether your database supports the TOP or LIMIT keyword to restrict the result set to the top three rows, e.g.

 
select TOP 3 * 
  from mytable 
 order by CODIGO desc

You would also have to know how to get at the top, second, and third rows using the programming features of your software or script.


This was first published in July 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: