query for most common 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)

query for most common value

Author

Topic

bill_
Starting Member

38 Posts

Posted - 2010-05-04 : 14:20:54

If each row has a date, how do you do a query for the date that shows up in the most frequently ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 14:22:23

[code]SELECT TOP 1 date
FROM
(
SELECT date,COUNT(1) OVER (PARTITION BY date) AS Occur
FROM Table
)t
ORDER BY Occur DESC
[/code]

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

bill_
Starting Member

38 Posts

Posted - 2010-05-05 : 12:12:01

Thank you but had no luck with that. Just gets most recent row.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-05 : 12:45:34

Try this:


SELECT TOP(1) T.date
  FROM YourTable AS T
 GROUP BY T.date
 ORDER BY COUNT(*) DESC

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-05 : 13:40:33

quote:
Originally posted by bill_

Thank you but had no luck with that. Just gets most recent row.

Can you show some sample data and expected output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 14:48:40

quote:
Originally posted by bill_

Thank you but had no luck with that. Just gets most recent row.

what do you mean by most recent row? can you give some sample and explain?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources