How to get the second biggest number using sql query?

Hi,

Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable.

For example, I have the following records:

NumColumn

1

2

3

4

10

11

18

If I use "select Max(NumColumn) from aTable", I will get the number: 18.

But I need to use SQL to the second biggest number: 11.

How to do that? Thanks.

Hi,

Try this

Code Snippet

select max(NumColumn) from aTable where NumColumn<=(select max(NumColumn) from atable)

-- Edited (Previous query i wrote was for Mysql.sorry about that)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Or this...

Code Snippet

SELECT MAX(NumColumn) FROM aTable WHERE NumColum < (SELECT MAX(NumColumn) FROM aTable)

|||

Hi,

To calculate the nth highest no.

Code Snippet

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Try:

if the values are unique:

with cte

as

(

select *, row_number() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

if the values are not unique

with cte

as

(

select *, dense_rank() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

Example:

Code Snippet

use tempdb

go

declare @.t table (

NumColumn int

)

insert into @.t values(1)

insert into @.t values(2)

insert into @.t values(3)

insert into @.t values(4)

insert into @.t values(10)

insert into @.t values(11)

insert into @.t values(18)

;with cte

as

(

select *, row_number() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

insert into @.t values(1)

;with cte

as

(

select *, dense_rank() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

go

AMB|||

Suprotim - your queries don't work as advertised

@.temp = 2 this gives you the the max

@.temp = 3 gives 2nd highest

|||

What if you have

NumColumn

1

2

3

4

10

11

17

17

17

18

18

What number should be returned?|||

Hi Sql-pro,

Which one doesn't work?

I tried it against this :

NumColumn

1

2

3

4

10

11

18

Suprotim

|||

Hi,

Sorry, not trying to be a jerk but all 3 of them didn't work against 1, 2, 3, 4

Code Snippet

select max(AccountID) from Check_Account where AccountID<=(select max(AccountID) from Check_Account)

go

= 4

--2nd highest

Declare @.temp int

set @.temp = 1

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

go

= 4

--3rd highest

Declare @.temp int

set @.temp = 2

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

= 3

|||

Is your NumberColumn guaranteed to be unique? In other words, might you have values like this:

NumColumn

1

1

2

3

4

4

4

5

5

6

7

8

8

8

9

9

If so, what would the number you would like returned?

|||

Dear rusag,

Modified the query :

Code Snippet

Declare @.temp int

set @.temp = 2

;with cte

as

(

select DISTINCT * from atable

)

SELECT MAX(NumColumn) from cte WHERE NumColumn NOT IN ( SELECT TOP (@.temp -1) NumColumn FROM cte ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Dear SqlPro,

Code Snippet

I took the data 1,2,3,4

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 3

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 2

How is it that you get different results?

Suprotim

|||

Well if you look at your 2nd highest query

You have @.temp = 1

and your subquery is SELECT TOP @.temp - 1

1 - 1 = 0 so your subquery returns nothing, and therefore your just getting the max

it's all semantics anyway, I think your approach is fine, just need to know that

@.temp = 2 returns 2nd highest

@.temp = 3 return 3rd highest

|||

Hai,

Try the following query

select NumColumn from
aTable t1
where N=(

select count(distinct t2.NumColumn)

from aTable t2 where t2.NumColumn>t1.NumColumn

)

Where N= position-1

If uwant to find second largest N should be =1 (2-1)