Q

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.

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

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close