extract the most common (highest count) entry by group-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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)