Statistical Mode calc in SQL

[DianeJ]

I'm looking for the best way to implement the statistical mode calculation in SQL.

Mode is value in a list that occurs the most often. For example I have a table (Temp) that has a column (TempCol) with the following rows:
a
b
a
c
a
a

The mode of these rows would be 'a'.

This is the code that I have so far, is it the best?

select distinct_values.tempcol
from (select tempcol, count(tempcol) occurence
from temp
group by tempcol) distinct_values
where distinct_values.occurence =
(select max(distinct_values.occurence)
from (select tempcol, count(tempcol) occurence
from temp
group by tempcol) distinct_values)


Thanks
Diane

 

[Dagon]

You could use a rank function:

select * from
(select tempno, count(tempno),
rank() over (order by count(tempno) desc) as occur_rank
from temp
group by tempno)
where occur_rank < 2

 

[srishan]

SELECT TempCol FROM
(SELECT TempCol, rownum rn FROM
(select count(*) cnt, TempCol
from Temp
group by TempCol)
Order by cnt desc)
WHERE rn = 1

Will also work, If you are not familiar with Analytical functions.