Thread: 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?

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

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;

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)

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)

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