SQL order to have max-1, max-2, max-3 ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Feb 2003
    Posts
    23

    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.

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    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
  •  



Click Here to Expand Forum to Full Width