Results 1 to 6 of 6

Thread: Second highest number from a list of numbers

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    May 2007
    Answers
    2

    Second highest number from a list of numbers

    Hi,

    I want to find the second highest number from a list of numbers?

    Ex. How to find the second highest "unitprice" from "products" table in "northwind" database?


  2. #2
    Expert Member
    Join Date
    Dec 2006
    Answers
    204

    Re: Second highest number from a list of numbers

    You have posted the same thing again? Or is my answer is wrong?


  3. #3
    Contributing Member
    Join Date
    Apr 2007
    Answers
    46

    Re: Second highest number from a list of numbers

    Hi,

    For getting second highest number u can try following query:-

    select unitprice from (select rownum r, unitprice from (select unitprice from products order by unitprice desc)) where r=2;


  4. #4

    Re: Second highest number from a list of numbers

    select max(list_number) from table_name
    where list_number not in (select max(list_number) from table_name)



    Note: table_name =put name of the table
    list_number = column name(containing numbers)


  5. #5
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: Second highest number from a list of numbers

    hi
    check this -------->(i find it better way to find nth highest in query)

    11:12:31 SQL> ed
    Wrote file afiedt.buf

    1 select SUBJECT,MARKS,nth
    2 from
    3 (select SUBJECT,MARKS,
    4* dense_rank() over(order by marks)nth from test2)
    11:13:37 SQL> /

    SUBJECT MARKS NTH
    -------------------- ---------- ----------
    English 85 1
    History 89 2
    Science 97 3
    Maths 100 4
    Maths 100 4

    Elapsed: 00:00:00.00

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5 Bytes=190)
    1 0 VIEW (Cost=4 Card=5 Bytes=190)
    2 1 WINDOW (SORT) (Cost=4 Card=5 Bytes=55)
    3 2 TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=3 Card=5
    Bytes=55)




    11:13:39 SQL> ed
    Wrote file afiedt.buf

    1 select SUBJECT,MARKS
    2 from
    3 (select SUBJECT,MARKS,
    4 dense_rank() over(order by marks)nth from test2)
    5* where nth=2
    11:14:01 SQL> /

    SUBJECT MARKS
    -------------------- ----------
    History 89

    Elapsed: 00:00:00.00

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5 Bytes=190)
    1 0 VIEW (Cost=4 Card=5 Bytes=190)
    2 1 WINDOW (SORT PUSHED RANK) (Cost=4 Card=5 Bytes=55)
    3 2 TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=3 Card=5
    Bytes=55)


  6. #6
    Junior Member
    Join Date
    Jun 2007
    Answers
    18

    Re: Second highest number from a list of numbers

    CREATE OR REPLACE VIEW table_view
    AS
    SELECT col1,rownum AS level FROM table
    ORDER BY col1;
    THIS query genertes col1 ordered by col1
    and level wld 2 for 2nd higest value,
    NOW
    SELECT * FROM table_view
    WHERE rownum=2


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact