-
SQL order to have max-1, max-2, max-3 ...
We want to have the max, max-1, ..., max-9 of a table column.
So, suppose that you have a table with 1 column (C1) and with the following rows :
C1
---
1
2
2
3
3
4
5
6
7
8
9
11
13
17
- first sql statement to have the max-1 : 13
- second sql statement to have max-2 : 11
- ...
to have max-1, I used this statement :
select max(C1) from
(select C1 from (select C1 from z_tab where C1 < (select max(C1) from z_tab))
)
could you suggest the best method to have result desired.
-
max-1 :
Code:
select min(c1)
from (select c1 from z_tab order by c1 desc)
where rownum < 3
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
Re: SQL order to have max-1, max-2, max-3 ...
Originally posted by nour
- first sql statement to have the max-1 : 13
- second sql statement to have max-2 : 11
- ...
You want SQL Statements or Values in that order?
Later is pretty easy...
If you want SQL Statements then you can think of having some thing like this
Code:
TEST:ABHAY> ed
Wrote file afiedt.buf
Select 'Select * from TEST Where ID='||ID "SQL Statement" from TEST where ID != ( Select Max(ID) from TEST )
Order By 1 Desc
SQL Statement
-----------------------------------
Select * from TEST Where ID=4 -- Max-1
Select * from TEST Where ID=3 -- Max-2
Select * from TEST Where ID=2 ..
Select * from TEST Where ID=1 ..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|