Statistical SQL - 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)

Statistical SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-26 : 09:44:02

Eddie writes "hi everybody!

is there any way that I can use T-SQL to retreive the most commonly occuring integer value in a column?

eg if the data in the column is 1,2,3,2,2

then the function will return 2

TIA,

eddiec :-)"

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2001-11-26 : 09:51:49

Interesting question - try this:

SELECT TOP 1 FROM (
SELECT COUNT(*) AS C,Number FROM tblTable GROUP BY Number ORDER BY COUNT(*) DESC
) AS S1

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2001-11-26 : 10:55:48

I think before you can use that SQL statement you're going to have to convert that field into a temporary table. I'd look at this article: http://www.sqlteam.com/item.asp?ItemID=2652

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2001-11-26 : 11:44:02

You're right graz - that SQL statement would have given an error. Here's the corrected version:

SELECT TOP 1 * FROM (
SELECT TOP 100 PERCENT COUNT(*) AS C,IntField FROM tblTable GROUP BY IntField ORDER BY COUNT(*) DESC
) AS S1

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-26 : 14:58:46

Moving the ORDER BY:

SELECT TOP 1 * FROM (
  SELECT COUNT(*) AS C, IntField FROM tblTable GROUP BY IntField) AS S1
ORDER BY C DESC

no difference in performance, but at least you can use WITH TIES if necessary.

Edited by - Arnold Fribble on 11/26/2001 15:10:59

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources