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)
|||SELECT * FROM TABLE WHERE CONDITION ORDER BY DESC FIELDNAME LIMIT 1,1sql