CODEWITHSUNDEEP
mysqlsql
jasonslyvia
jasonslyvia

Reputation: 2535

How to get most appeared combination using SQL?

I have a table storing log entries, a simplified version would be 

key value
a   1
a   2
a   3
a   2
b   1
b   2
b   2

and I just want to extract the most appeared combination like:

key  value
a    2
b    2

since a-1 and a-3 combination come up only once, and a-2 combination is twice.

any kind of SQL solution is welcomed, I just want to know the gist to tackle this issue, I will find the right solution for my own platform / sql variant.

based on Gordon Linoff's great answer and the abilities my platform can provide, I end up using something like this:

select 
  key,
  value
from (
  select
    key,
    value,
    rank() over(partition by key order by t1.cnt desc) as rank
  from (
    select
      key,
      value,
      count(*) as cnt
    from 
      table
    group by
      key,
      value
  ) t1
  group by
    key,
    value,
    t1.cnt
) t2
where t2.rank = 1

Upvotes: 0

Views: 57

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

This is a pain in MySQL, but you can do:

select key, value, count(*) as cnt
from t
group by key, value
having cnt = (select count(*)
              from t
              group by key, value
              order by count(*) desc
              limit 1
             );

If you are using MySQL version 8 or just about any other database, use rank() or window functions:

select key, value, cnt
from (select key, value, count(*) as cnt,
             max(count(*)) over () as max_cnt
      from t
      group by key, value
     ) t
where cnt = max_cnt;

Upvotes: 1

Sam
Sam

Reputation: 110

Try This

DECLARE @T TABLE  (key_ varchar(10),Val int)
INSERT INTO @T
SELECT 'a' Key_, 1 Val UNION ALL
SELECT 'a' k, 2 v UNION ALL
SELECT 'a' k, 3 v UNION ALL
SELECT 'a' k, 2 v UNION ALL
SELECT 'b' k, 1 v UNION ALL
SELECT 'b' k, 2 v UNION ALL
SELECT 'b' k, 2 v

SELECT T.key_,T.Val FROM (SELECT key_,Val,Row_Number()Over(Partition By Key_,Val Order By key_ ) Count_ FROM @T) T INNER JOIN 
(SELECT MAX(Count_) Count_ FROM (SELECT Row_Number()Over(Partition By Key_,Val Order By key_ ) Count_ FROM @T)X) XX ON XX.Count_ = T.Count_

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

I'm okay with either combination if there were a tie

You could try this

SELECT `key`
    ,`value`
FROM t
GROUP BY `key`
    ,`value`
ORDER BY count(*) DESC LIMIT 1

Upvotes: 1

Related Questions