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.
Re: SQL order to have max-1, max-2, max-3 ...
Quote:
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.