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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.