# Thread: Second highest number from a list of numbers

1. ## 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. ## Re: Second highest number from a list of numbers

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

3. ## 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. ## 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. ## 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. ## 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
•