How to get second largest or third largest entry from a table

Question

Can anyone please tell me how to find out the N th largest entry from a table in Oracle?

Like for largest we can use MAX(column_name) is there any efficient way to find nth largest also?

score 25 · Accepted Answer · edited Jul 04 '16 at 13:35

25

SELECT *
FROM (
  SELECT some_column, 
         row_number() over (order by your_sort_column desc) as row_num
  FROM some_table
) t
WHERE row_num = 3

If you expect more than one row to have the same value in your_sort_column you can also use the rank() function

SELECT *
FROM (
  SELECT some_column, 
         rank() over (order by your_sort_column desc) as row_rank
  FROM some_table
) t
WHERE row_rank = 3

This migh return more than one row..

edited Jul 04 '16 at 13:35

DerMike

15,594
13
50
63

answered Dec 19 '10 at 15:37

1

+1: Only caveat is analytic functions are supported on 9i+. And DENSE_RANK might be a better choice than RANK, depending on needs. – OMG Ponies Dec 19 '10 at 19:28
Not true. Analytical functions were available in 8i: http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#81409. But 8i and 9i are de-supported anyway. So there is no reason to post answers covering those versions if not explicitely mentioned – Dec 19 '10 at 21:18

score 7 · Answer 2 · edited Jun 08 '20 at 07:34

7

You can find the Nth largest value of a column by using the following query

SELECT * 
FROM TableName a 
WHERE n = (
           SELECT count( DISTINCT (b.ColumnName) )
           FROM TableName b 
           WHERE a.ColumnName <= b.ColumnName 
          );

edited Jun 08 '20 at 07:34

Mostafa_Reda_1

26
5

answered Jan 23 '13 at 06:19

Abhishek B Patel

887
10
13

2

Welcome to Stack Overflow! Thanks for posting your answer! Please be sure to read the [FAQ on Self-Promotion](http://stackoverflow.com/faq#promotion) carefully. Also note that it is *required* that you post a disclaimer every time you link to your own site/product. – Andrew Barber Jan 23 '13 at 06:29

score 5 · Answer 3 · edited Jun 08 '20 at 06:43

5

I think the below query will work to find the second highest record with NOT IN.

SELECT MAX( userId )
FROM table 
WHERE userId NOT IN ( 
                      SELECT MAX( userId )
                      FROM table
                    );

simple and useful...

edited Jun 08 '20 at 06:43

Mostafa_Reda_1

26
5

answered Feb 07 '12 at 06:14

prakash

99
1
7

score 1 · Answer 4 · edited Sep 28 '12 at 06:40

1

To get second largest salary use this:

select salary from 
  (select s2.salary,rownum rm from
     (select distinct salary from employees order by salary desc)
  s2 where rownum<=2)
where rm >= 2

edited Sep 28 '12 at 06:40

Andrew Barber

39,603
20
94
123

answered Aug 26 '12 at 06:47

Sanjeev Yadav

11
1

works perfectly but still I am not understand why query is not working after modifying like below MODIFIED: select s2.salary,rownum rm from (select distinct salary from employees order by salary desc) s2 where rm<=2 – Abilash Nov 26 '12 at 05:27

score 1 · Answer 5 · edited Apr 27 '15 at 10:24

1

It works for second highest salary,

$query = "SELECT * FROM `table_name` ORDER BY field_name` DESC LIMIT 1 , 1 ";

edited Apr 27 '15 at 10:24

Zeeshan

1,659
13
17

answered Oct 06 '13 at 16:27

codegeek

230
1
3
12

score 0 · Answer 6 · edited Apr 27 '15 at 10:45

0

Try this,

SELECT Sal FROM Tab ORDER BY Sal DESC LIMIT 2,1

edited Apr 27 '15 at 10:45

Zeeshan

1,659
13
17

answered Dec 14 '13 at 06:11

sudhakar reddy karukala

253
1
4
12

score 0 · Answer 7 · edited Apr 27 '15 at 10:07

0

Try this:

SELECT DISTINCT TOP 3 id,[Password] 
FROM Users_changepassword 
WHERE [UserId] = 3
ORDER BY id DESC

edited Apr 27 '15 at 10:07

ByteHamster

4,884
9
38
53

answered Apr 27 '15 at 10:03

user2326989

9
1

score 0 · Answer 8 · answered Nov 20 '15 at 21:29

0

You can try this sql where Row_number() function of the oracle sql is used

select column_name from (
 select column_name ,  
row_number() over (order by column_name  desc) as  row_num  
from table_Name ) tablex
where row_num =3

answered Nov 20 '15 at 21:29

ankursingh1000

1,349
1
15
21

score 0 · Answer 9 · edited Jan 05 '16 at 07:32

0

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)

edited Jan 05 '16 at 07:32

Tomasz Jakub Rup

10,502
7
48
49

answered Jan 05 '16 at 07:05

Piyush

11
3

Agos · Answer 10 · 2010-12-19T14:32:31.740

0

You can ORDER BY column name and then LIMIT 1,1 to get the second one

edit

Whoops, didn't see the Oracle tag, sorry.
ORDER BY column name WHERE ROWNUM = 2 should work better.

edited Dec 19 '10 at 14:32

answered Dec 19 '10 at 14:03

Agos

18,542
11
56
70

1

Oracle doesn't support LIMIT. – Ronnis Dec 19 '10 at 14:27
3

"WHERE ROWNUM = 2" will never return any rows. – Jon Heller Dec 19 '10 at 14:58
2

From http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm: "Conditions testing for ROWNUM values greater than a positive integer are always false." You have to alias ROWNUM and then use that alias instead of ROWNUM. – Jon Heller Dec 19 '10 at 17:48

score 0 · Answer 11 · edited Jun 10 '11 at 03:11

0

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (
  SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal
);

Replace &N with you desired number. For example, 2 will give you the second largest salary.

If you are using PL/SQL, just execute the statement. It will prompt for N.

edited Jun 10 '11 at 03:11

Ken Bloom

57,498
14
111
168

answered Dec 19 '10 at 15:22

Kishore Kumar

21,449
13
81
113

Lukasz Szozda · Answer 12 · 2017-11-30T18:39:27.897

You could use CONNECT BY PRIOR by:

CREATE TABLE t(i INT, sal INT);
INSERT INTO t(i, sal)
SELECT 1,100 FROM dual UNION
SELECT 2,100 FROM dual UNION
SELECT 3,200 FROM dual UNION
SELECT 4,500 FROM dual UNION
SELECT 5,1000 FROM dual;

Query:

SELECT level, MAX(sal) AS sal
FROM t
--WHERE level = 2 -- set position here
CONNECT BY prior sal > sal
GROUP BY level
ORDER BY level;

DBFiddle Demo

DBFiddle Demo2

EDIT:

Second approach is to use NTH_VALUE analytic function:

SELECT DISTINCT NTH_VALUE(sal, 2) OVER(ORDER BY sal DESC
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM t;

DBFiddle Demo3

How to get second largest or third largest entry from a table

12 Answers12

Linked

Related