Get most frequent value - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Get most frequent value

Author

Topic

damian78
Starting Member

4 Posts

Posted - 2009-07-24 : 05:20:28

Hi All,

I've got a number of sql functions that calculate scores. These scores are as totals, averages, percentages and mode (most frequent value). Most are working, but I'm having a bit of trouble with Mode.

The following code kind of returns the data I require, but because it's returning 2 values (the value and the count) I can't set it to a variable.


SELECT TOP 1 [Value], 
    (SELECT COUNT(*) 
     FROM   ICS_DashboardData dd2
     WHERE  dd2.[Value] = dd.[Value]) AS cnt
FROM ICS_DashboardData dd
ORDER BY cnt DESC)

How could I get a query to return just the value?

Any help would be much appreciated.

Damian

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-24 : 05:26:25

[code]
select [Value] from
(
SELECT TOP 1 [Value],
(SELECT COUNT(*)
FROM ICS_DashboardData dd2
WHERE dd2.[Value] = dd.[Value]) AS cnt
FROM ICS_DashboardData dd
ORDER BY cnt DESC
)dt
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

damian78
Starting Member

4 Posts

Posted - 2009-07-24 : 05:34:02

Thanks for the reply webfred.

I actually just found the following solution:

SELECT	TOP 1 WITH TIES [Value]
FROM	ICS_DashboardData
GROUP BY [Value]
ORDER BY COUNT(*) DESC

though thinking about it I'm testing on Sql 2005, but the application is published on 2000 so not sure if this will work.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-24 : 05:54:19

I would take away "WITH TIES" if you need to take the result into a variable.
With TIES can give more than one row in the result set.
See here:

select top 1 with ties ttt from
(
select 1 as ttt union all
select 1 as ttt
)dt
order by ttt

No, you're never too old to Yak'n'Roll if you're too young to die.

damian78
Starting Member

4 Posts

Posted - 2009-07-24 : 05:57:05

A colleague has just pointed out that "WITH TIES" isn't actually needed - all this does is output all values that have the highest count (for example a leaderboard, if there are joint leaders each would be output, regardless of the "TOP 1"). So the solution is actually:


SELECT	TOP 1 [Value]
FROM	ICS_DashboardData
GROUP BY [Value]
ORDER BY COUNT(*) DESC

damian78
Starting Member

4 Posts

Posted - 2009-07-24 : 05:57:40

Thanks webfred, got in before me :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources