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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation