In SQL, for the same set of ID, I want to create the column Action with New where the Contract Number is the largest and Old for the smaller values.

For example, for ID = 123, the largest value in Contract Number is 10, so that row will be marked as New and the others as Old. The only columns in the SQL database are ID and Contract Number, so I'm assuming I should use a CASE WHEN statement to create the Action column, but not sure how to formulate it

Sample table:

Expected output:

CodePudding user response：

Here's a possible solution using SQL Server:

--temp table variable to simulate your acutal DB table
declare @tbl table ([ID] int, [Contract Number] int);
--insert the values given in the example data
insert into @tbl
values(123, 10), (123, 5), (123, 3), (456, 3), (456, 2);


--create cte (common table expression) that holds the max (new) of each ID
with cte ([ID], [Contract Number])
as
(
    select [ID], MAX([Contract Number]) AS [Contract Number]
    from @tbl
    group by [ID]
)
--inner join the max on table to get the 'New' ones
select t.[ID], t.[Contract Number], 'New' as [Expected Outcome]
from @tbl as t
inner join  cte
on cte.[ID] = t.[ID] and cte.[Contract Number] = t.[Contract Number]
UNION --UNION the old ones
--LEFT JOIN to get any that don't exist in the cte as Old
select t.[ID], t.[Contract Number], 'Old' as [Expected Outcome]
from @tbl as t
left join cte
on cte.[ID] = t.[ID] and cte.[Contract Number] = t.[Contract Number]
where cte.[ID] is null
order by t.[ID], t.[Contract Number] DESC; --order by ID asc, Contract Number desc

CodePudding user response：

You could use a case expression together with a windowed aggregate:

select *, 
  case when 
    contractnumber = Max(ContractNumber) over(partition by Id) then 'Keep'
  else 'Old' end Action
from sampledata;