declare @tb table([ID] nvarchar(3),[number] int) Insert @tb select N'001',50 union all select N'001',80 union all select N'001',50 union all select N'002',100 union all select N'002',60 union all select N'002',635 union all select N'003',90 union all select N'003',90 union all select N'003',20 select [ID],max([number]) as [number] from @tb group by [ID] /* ID number ---- ----------- 001 80 002 635 003 90 */ Select distinct * from @tb t where not exists(select 1 from @tb where [ID] = t.[ID] and [number]>t.[number]) /* ID number ---- ----------- 001 80 002 635 003 90 */
declare @tb table([ID] nvarchar(3),[number] int)
Insert @tb
select N'001',50 union all
select N'001',80 union all
select N'001',50 union all
select N'002',100 union all
select N'002',60 union all
select N'002',635 union all
select N'003',90 union all
select N'003',90 union all
select N'003',20
select [ID],max([number]) as [number] from @tb group by [ID]
/*
ID number
---- -----------
001 80
002 635
003 90
*/
Select distinct * from @tb t
where not exists(select 1 from @tb where [ID] = t.[ID] and [number]>t.[number])
/*
ID number
---- -----------
001 80
002 635
003 90
*/