SQL max value per group ignoring less valued rows with differing values-CodePudding

I would need a max value per group so that result set ignores lesser valued rows; even if they have different values. Here is the example:

I'm attempting to maximize the person_id (same person has many person_ids) for every person, team and division and show only that row. Here is the simple sql that I tried.

SELECT max(person_id) as person_id, person_name, division_name, team_name
from organization_table
group by
person_name, 
division_name, 
team_name

My question is, how would I query this dataset so that SQL would return only the row with max personid for each person regardless differing team and division values?

Thanks!

CodePudding user response：

You can give your records a rank, based on the highest person_id (order by person_id desc) per (partition by) person_name first by using a CTE and then selecting only the highest ranked record:

WITH CTE AS (
  SELECT 
    *, 
    ROW_NUMBER() OVER (PARTITION BY person_name ORDER BY person_id DESC) AS RANK
  FROM organization_table
)
SELECT * FROM CTE WHERE RANK = 1;