SQL order to have max-1, max-2, max-3 ...

[nour]

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.

[TomazZ]

max-1 :

select min(c1) 
  from (select c1 from z_tab order by c1 desc)
 where rownum < 3

[abhaysk]

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

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.