Second highest value from Oracle DB's table

Question

According to the tables:

USERS (user_name, email, balance)

How can I create a query that return the second highest user balance in the most efficient way ?

I successes to get this record (but not by the efficient way) with the query:

SELECT 
  * 
FROM  
  (SELECT 
    us.*,
    ROWNUM row_num
  FROM  
    (SELECT 
      u.*
    FROM
      users u
    ORDER BY
      u.BALANCE DESC) us
  WHERE
    ROWNUM < 3)
WHERE
  row_num > 1;

You may get some idea from http://stackoverflow.com/questions/38990303/display-city-with-second-highest-number-of-stores-in-sql/38990690#38990690 — Jaydip Jadhav, Aug 23 '16 at 11:35
What do you want returned if there are ties for the highest value? — Gordon Linoff, Aug 23 '16 at 11:35
Your code is fine, assuming you have an index on `users(Balance)`. — Gordon Linoff, Aug 23 '16 at 11:37

score 2 · Answer 1 · answered Aug 23 '16 at 11:37

2

I would use a window function:

select *
from (
  select u.*, dense_rank() over (order by balance desc) as rnk
  from users u
) t
where rnk = 2;

I don't think there will be a big performance difference to your query (especially not with an index on balance) but in my opinion it's easier to read and maintain.

answered Aug 23 '16 at 11:37

suggestion: instead of dense_rank() better to use row_number() – Aishu Aug 23 '16 at 15:59
@Aishu: but that wouldn't return multiple users with the same balance that is the second highest. It would only return a single user even if there is another one with the same (second highest) balance. – Aug 23 '16 at 16:03

Luthando Ntsekwa · Answer 2 · 2016-08-23T11:44:18.017

1

Try this:

SELECT * 
FROM (SELECT *   
      FROM USERS   
      ORDER BY balance DESC  
      FETCH FIRST 2 ROWS ONLY
      ) 
ORDER BY balance DESC  
FETCH FIRST 1 ROWS ONLY

edited Aug 23 '16 at 11:44

answered Aug 23 '16 at 11:36

Luthando Ntsekwa

4,192
6
23
52

1

It'll be nice to compare this 2 methods. Probably this one is faster. – vercelli Aug 23 '16 at 11:54
1

Keep in mind that if OP wants duplicates he should use `WITH TIES` – vercelli Aug 23 '16 at 11:57

score 0 · Answer 3 · edited Aug 23 '16 at 12:47

0

This should work even in case of more than one user having same 2nd largest balance..

select * from USERS where balance IN
  (select max(balance) 
     from (select balance from USERS 
    where balance NOT IN (select max(balance) from USERS))
);

edited Aug 23 '16 at 12:47

J. Chomel

8,193
15
41
69

answered Aug 23 '16 at 12:39

NIRMAL K M

17
2

Second highest value from Oracle DB's table

3 Answers3

Related