-3

I'm trying to write an SQL statement to return the second largest applicationid anyone got any idea of how to do this?

This was a question ask of me so I do not have any code to start, I was thinking that SQL has a built in method to do this.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Filling The Stack is What I DO
  • 6,553
  • 14
  • 54
  • 86
  • 3
    Seveveral answers to this already exist: http://stackoverflow.com/questions/32100/what-is-the-simplest-sql-query-to-find-the-second-largest-value is one ... or http://stackoverflow.com/questions/80706/query-to-find-nth-max-value-of-a-column or http://stackoverflow.com/questions/14196427/how-to-get-nth-highest-value-using-plain-sql. each has it's own merit based on version of SQL-server and use cases. couldn't second become nth? – xQbert May 21 '15 at 19:50

3 Answers3

2

If you are using SQL Server 2012 and ApplicationId is unique in the table being queried:

select ApplicationId
from Applications
order by ApplicationId desc
offset 1 fetch first 1 row only;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You could use the rank window function:

SELECT applicationid
FROM   (SELECT applicationid,
               DENSE_RANK() OVER (ORDER BY applicationid DESC) AS rk
        FROM   mytable)
WHERE  rk = 2
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 1
    Id `applicationid` is not unique there might be no rank 2 (e.g. 1-1-3), you better use `DENSE_RANK` instead – dnoeth May 21 '15 at 20:33
0
select ApplicationId
from Applications a1 where 3=(select count(ApplicationId)
from Applications a2 where a2.ApplicationId>a1.ApplicationId)
harsh pareek
  • 61
  • 7