Second Highest Value From Oracle Db's Table

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

Solution 1:

I would use a window function:

select*from (
  select u.*, dense_rank() over (orderby 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.

Solution 2:

Try this:

SELECT*FROM (SELECT*FROM USERS   
      ORDERBY balance DESCFETCHFIRST2ROWSONLY
      ) 
ORDERBY balance DESCFETCHFIRST1ROWSONLY

Solution 3:

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

select*from USERS where balance IN
  (selectmax(balance) 
     from (select balance from USERS 
    where balance NOTIN (selectmax(balance) from USERS))
);