getting highest count month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting highest count month

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-09-18 : 09:43:29

Hi,
I am having query which returns the month and the count of address ot that month. How to form query to get the month which has highest count?

SELECT
DATEDIFF(MM , tsl.LoadDate , GETDATE()) as MonthRecency ,
COUNT(DISTINCT adr.AddressID) AS COUNT
FROM
person ind with(nolock)
inner join
Address adr with(nolock)
on ind.AddressID = adr.AddressID
inner join
telephone tsl with(nolock)
on adr.Telephone = tsl.Telephone and
group By
DATEDIFF(MM , tsl.LoadDate , GETDATE())

Thanks
Subha

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-18 : 10:00:34

Hi
To get MonthRecency which has highest count..........

SELECT TOP (1) WITH TIES
DATEDIFF(MM , tsl.LoadDate , GETDATE()) as MonthRecency ,
COUNT(DISTINCT adr.AddressID) AS COUNT
FROM ........
........
ORDER BY COUNT DESC

--
Chandu

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-09-18 : 10:04:42

SELECT Top 1 A.MonthRecency,A.cnt
FROM
(SELECT
'4' As MonthRecency,
'13' AS CNT

UNION ALL

SELECT
'3' As MonthRecency,
'10' AS CNT

UNION ALL

SELECT
'5' As MonthRecency,
'20' AS CNT

)A
order by
Row_Number() Over (order by A.CNT) desc

I am not sure of the performance on huge table
, I think you should try other approach if you are having huge amount of data,
bcoz here i have used order by desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 10:50:14

quote:
Originally posted by Vishal_sql

SELECT Top 1 A.MonthRecency,A.cnt
FROM
(SELECT
'4' As MonthRecency,
'13' AS CNT

UNION ALL

SELECT
'3' As MonthRecency,
'10' AS CNT

UNION ALL

SELECT
'5' As MonthRecency,
'20' AS CNT

)A
order by
Row_Number() Over (order by A.CNT) desc

I am not sure of the performance on huge table
, I think you should try other approach if you are having huge amount of data,
bcoz here i have used order by desc

this will not work as intended always

for example if you've two months with same highest count of address this will just return one of them and not both

use dense_rank instead of row_number to get all in case of a tie

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-09-18 : 11:04:57

Agreed.

thanks Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 11:17:22

No problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources