Most Frequent Occuring 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 2005 Forums

Transact-SQL (2005)

Most Frequent Occuring Value

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-11 : 09:52:37

In Excel I use this function to find the Most Frequent Occuring or Repetitive Value in a range.
MODE(O2:INDEX(O:O,S11))

How can I do the same thing in an SQL Server Query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 09:57:05

[code]
select Value, count(*)
from yourtable
group by Value
order by count(*) desc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-11 : 10:11:28

When I do that Query it then gives me more than 1 value back. How can I only show 1 record?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 10:14:51

select TOP 1 Value, count(*)

KH
[spoiler]Time is always against us[/spoiler]

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-16 : 04:56:45

DECLARE @STR VARCHAR(100)
SET @STR= 'KAMARAJ'

SELECT TOP 2 MAXREC.*
FROM
(
SELECT CHARTEXT,[ORDERCOUNT]=COUNT(*)
FROM
(
SELECT [CHARTEXT]=SUBSTRING(@STR,T.NUMBER,1)
FROM
(
SELECT NUMBER FROM MASTER..SPT_VALUES WHERE NUMBER BETWEEN 1 AND LEN(@STR) AND TYPE = 'P'
) T
) C
GROUP BY C.CHARTEXT
) MAXREC
ORDER BY MAXREC.ORDERCOUNT DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 16:59:17

See http://www.sqlteam.com/FORUMS/topic.asp?TOPIC_ID=75441

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources