CODEWITHSUNDEEP
mysqlsql
user6269144
user6269144

Reputation: 318

SQL - calculate MODE without sorting

I would like to calculate the MODE of a single column in SQL. This is done easily enough with:

SELECT v AS Mode  
FROM Data  
GROUP BY v HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM Data GROUP BY v);

However, I would like to do this without sorting, i.e. without using GROUP BY or any similar construct. Is there a quick and easy way to do this?

Upvotes: 3

Views: 409

Answers (2)

cha
cha

Reputation: 10411

For MySQL the best I could come up with is this:

select distinct v from(
select v,
@cnt := (select count(*) from Data d1 where d1.v=d.v) as cnt_,
case when @cnt>=@max then @max:=@cnt end as max_
from Data d,
(select @max:=1, @cnt:=1) c) a
where cnt_ = @max

For SQL Server, Oracle or Postgres you can use a window function:

with a as (
v, select row_count() OVER(PARTITION BY v) rn
from Data
)
select v as Mode
FROM a
where rn = (select max(rn) from a)

Upvotes: 0

grovkin
grovkin

Reputation: 158

group by doesn't do sorting. It does partitioning. So instead of 1 aggregate result, you get 1 result per group in which all values that you group by are the same.

Upvotes: 1

Related Questions