SQL Server 2008 R2: Find a second largest number in the table

Question

I have following table with some data to find second largest number.

Table:

ColA
---------
3
23
43
673
173
373
273

@Amit A simple question doesn't indicate homework/course. However , regarding self effort - I agree. — Royi Namir, Jul 19 '15 at 11:03
@RoyiNamir - So why do you answer such a question? You shouldn't encourage others to ask such questions by rewarding them with answers, you should de-motivate. Downvote. — Amit, Jul 19 '15 at 11:05
@Amit He asks a question. If he wants other people to solve his exercises without learning - that's his problem. He will lose from it . not me. I will be a better programmer since I solved one more drill. My downvote won't help anyone here. I would've downvote if the question was purely written , unclear etc. _de-motivate_ ( as you said) never helped anyone , nor will. — Royi Namir, Jul 19 '15 at 11:06
Most of the answers from the link http://stackoverflow.com/questions/16234983/how-to-find-third-or-nth-maximum-salary-from-salary-table return wrong results when the same salary exists multiple times, e.g. 10,20,30,30 -> 2nd highest is 20, not 30. Instead of ROW_NUMBER one must use DENSE_RANK instead. — dnoeth, Jul 19 '15 at 11:10

Royi Namir · Accepted Answer · 2015-07-19T11:15:18.303

~~Use this:~~

select * from 
(select colA  , row_number() over(order by colA desc) as rn from Table) T
where T.rn=2

Thanks to Martin's / dnoeth's comment :

It can be problematic with 2 max inetegers

Use this :

  select * from 
    (select colA  , DENSE_RANK( ) OVER (ORDER BY colA  ) as rn from Table) T
    where T.rn=2

Additional info : (once and for all ( learn it! , I always use it)) :

DECLARE @t TABLE(NAME NVARCHAR(MAX),val money)

insert INTO @t SELECT 'a',100
insert INTO @t SELECT 'a',100
insert INTO @t SELECT 'a',100
insert INTO @t SELECT 'a',100
insert INTO @t SELECT 'b',200
insert INTO @t SELECT 'b',200
insert INTO @t SELECT 'd',400
insert INTO @t SELECT 'e',500
insert INTO @t SELECT 'f',600
select  Name,
        val,
        ROW_NUMBER() OVER (PARTITION BY NAME  ORDER BY name),
        val/ SUM(val) OVER(PARTITION BY NAME )  AS '1AgainstTotalHimself',
        val/ SUM(val) OVER( )  AS '1AgainstOthers' , 
        NTILE(2) OVER ( PARTITION BY NAME ORDER BY name) AS 'ntile2' ,
        NTILE(2) OVER ( ORDER BY name) AS 'ntile' , -- ( 9%2=1  , so group #1 will get more number) 
        RANK( ) OVER ( ORDER BY name  ) AS  Rank,
        DENSE_RANK( ) OVER (ORDER BY name) AS DENSERANK

from @t

Result :

enter image description here

Switch to DENSE_RANK instead of ROW_NUMBER to get the correct result when multiple rows share the same salary. — dnoeth, Jul 19 '15 at 11:12

Zohar Peled · Answer 2 · 2015-07-19T11:10:30.487

2

One way to solve this would be

with cte as (
    select ColA, 
           Row_number() over(order by ColA DESC) As rn
    FROM Table
)

SELECT ColA
FROM cte
WHERE rn = 2

Update After Martin's correct comment, here is a better answer:

Select top 1 ColA
From YourTable
WHERE ColA < (
   SELECT MAX(ColA) 
   FROM YourTable
)
ORDER BY ColA DESC

edited Jul 19 '15 at 11:10

answered Jul 19 '15 at 11:01

Zohar Peled

79,642
10
69
121

This might find the largest number if it appears twice. – Martin Smith Jul 19 '15 at 11:02
2

Or simply use DENSE_RANK instead of ROW_NUMBER – dnoeth Jul 19 '15 at 11:11
@MartinSmith I start thinking , my solution can also yield wrong result. – Royi Namir Jul 19 '15 at 11:11
1

@RoyiNamir yep. I thought one comment making the point was sufficient though rather than spamming all answers. – Martin Smith Jul 19 '15 at 11:13

SQL Server 2008 R2: Find a second largest number in the table

2 Answers2