How do we get the rows that contain the max value for the column "Term" in each grouped se-CodePudding

How do you get the rows that contain the max value for the column "Term" in each grouped set?

I've seen some overly-complicated variations on this question, and none with a good answer. I've tried to put together the simplest possible example:

Given a table like that below, with Module_Name , Term , and Synopsis columns, how would you get desired result as like below?

Module_Name | Term | synopsis

Ali | 2001 | case1
Ali | 2002 | case2
Ali | 2002 | case3

Nami | 2101 | Opt1
Nami | 2101 | Opt2
Nami | 2102 | opt3

Desired result set:

Ali | 2002 | case2
Ali | 2002 | case3

Nami | 2102 | opt3

CodePudding user response：

You can use rank window function:

with r as (
  select *, 
  rank() over(partition by Module_name order by term desc) rnk
  from t
)
select Module_name, Term, Synopsis
from r
where rnk = 1;

Demo DB<>Fiddle

CodePudding user response：

Here is solution that works using row_number partitioned by module_name. #test is your table name

WITH tempdata as
(
    SELECT ROW_NUMBER() OVER(PARTITION BY module_name
    ORDER BY term DESC) as RowNumber,
    module_name,
    term,
    synopsis
    FROM #test
)
SELECT td.module_name,td.term,td.synopsis 
FROM tempdata td JOIN #test t on td.module_name = t.module_name and td.term = t.term 
WHERE td.RowNumber = 1