DECLARE @Temp TABLE
(
n1 int,
n2 varchar(10),
n3 varchar(10),
n4 int
)
insert into @Temp
values(1,'a','x',5),
(1,'a','x',5),
(1,'a','y',5),
(1,'a','y',4),
(1,'b','y',1),
(2,'b','y',4),
(2,'c','y',2),
(2,'d','z',5),
(3,'e','n',2)
SELECT * FROM @Temp
select t.n1,
t.n2,
t.n3,
t.n4,
row_number() OVER(PARTITION BY t.n1,t.n2,t.n3,t.n4 ORDER BY t.n2 desc) rn
from @Temp t
select *
from (select t.n1,
t.n2,
t.n3,
t.n4,
row_number() OVER(PARTITION BY t.n1 ORDER BY t.n2 desc) rn
from @Temp t) t
where t.rn = 1
|