How do I get the second max using SQL?

Question

I had that question in a code interview, I found a possible solution but I want to hear from you so thanks for your comments and help.

Select max(ID) from table where ID not in (select max(ID) from table)

https://stackoverflow.com/questions/32100/what-is-the-simplest-sql-query-to-find-the-second-largest-value — Mitch Wheat, Jan 07 '18 at 14:11
@MitchWheat I've found the solution on google after my test and it was similar to mine but I don't see any problem hearing from experts like you here. — Eliú Abisaí Díaz Vásquez, Jan 07 '18 at 14:19

score 0 · Accepted Answer · answered Jan 07 '18 at 14:10

0

Use a window function:

select *
from (
   select x.*, 
          row_number() over (order by x.id) as rn
   from the_table x
) t
where rn = 2;

answered Jan 07 '18 at 14:10

1 Answers1