-
Junior Member
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?
-
Expert Member
Re: Second highest number from a list of numbers
You have posted the same thing again? Or is my answer is wrong?
-
Contributing Member
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;
-
Junior Member
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)
-
Contributing Member
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)
-
Junior Member
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
-
Forum Rules