extract the most common (highest count) entry by group

user_12549160 0
Viewed 75

I have the following table:

ID       height
personA  182
personA  182
personA  182
personA  192
personA  172
personB  175
personB  175

I would like to extract the most commonly appearing height for this individual as I suspect 192 was a typo. So far, I have:

select ID, height, count(ID,height) as cnt
from tbl
group by ID, height
having max(cnt);

My desired output is:

ID       height
personA  182
personB  175
snowflake-cloud-data-platform sql
4 Answers

You can simply use mode which is designed for your use case. Note that this won't handle ties

select id, mode(height) as height
from t
group by id;

Another alternative without using analytic functions that also handles ties

with cte as
(select id, height, count(*) as cnt 
from t
group by id, height)

select id, height
from cte
where (id, cnt) in (select id, max(cnt)
                    from cte
                    group by id)

If you were to implement the above using a qualify clause so cleverly used in Lukasz's answer, you could do

select id, height
from t
group by id, height
qualify max( count(*) ) over (partition by id) = count(*)
user_99471590

Using QUALIFY:

SELECT ID, height
FROM tab
GROUP BY ID, height
QUALIFY RANK() OVER(PARTITION BY ID ORDER BY COUNT(*) DESC) = 1;

RANK used to handle ties.

enter image description here

user_50708790

You can use a window function to rank the userids based on their count of height.

WITH cte AS (
SELECT 
    ID
  , height
  , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY COUNT(height) DESC) rn
FROM dbo.tbl
GROUP BY
  ID,
  height)

SELECT
    ID,
    height
FROM cte WHERE rn = 1

Also you can use max() function to get the largest entry by ID..

select ID, max(height)
from tbl
group by ID

should work.

user_177739200

You need to use the google analytic function. The analytic function will partition your table with your desired column. I have used row_number() function. You can also use the rank() function. To know more about the analytic function : https://hevodata.com/learn/bigquery-row-number-function/

Code:

Select ID, height
From (SELECT *,
            row_number() over(partition by id, height order by height 
                              desc) as row_number
      FROM students)
Group By ID
having max(row_number)
user_53938580
Related