Id name result
1 Tom 100
2 Jenny 80
3 Tom 60
4 Tom 50
5 Jenny 100
Someone who duplicate records in the table, could you tell me how to get the highest scores, each is like this:
Id name result
1 Tom 100
2 Jenny 100
CodePudding user response:
USE tempdb for
GO
IF OBJECT_ID (' dbo. [t]) IS NOT NULL
DROP TABLE dbo. [t]
GO
The CREATE TABLE dbo. [t] (
[id] NVARCHAR (MAX)
, [name] NVARCHAR (MAX)
, [result] the int
)
GO
SET NOCOUNT ON
INSERT INTO dbo. [t] VALUES (N '1', N 'Tom', N '100')
INSERT INTO dbo. [t] VALUES (N '2', 'Jenny' N, N '80')
INSERT INTO dbo. [t] VALUES (' 3 'N, N' Tom ', N '60')
INSERT INTO dbo. [t] VALUES (' 4 'N, N' Tom ', N '50')
INSERT INTO dbo. [t] VALUES (N '5', N 'Jenny', N '100')
-- -- -- -- -- -- -- to test the above table and test data -- -- -- -- -- -- --
The select row_number () over (order by id) as the rowId, id, name, the result
The from (
Select
Row_number () over (partition by the name the order by the result desc) as rids
, *
The from t
) as tt
Where tt. Rids=1
The order by id
/*
The rowId id name result
1 1 Tom 100
2 5 Jenny 100
*/
CodePudding user response:
The create table Performance (id int, name varchar (10), the result int)
Insert into Performance
Select 1, 'Tom', 100 union all
Select 2, 'Jenny', 80 union all
60 union all select 3, 'Tom',
50 union all select 4, 'Tom',
Select 5, 'Jenny', 100
Select *
From the Performance of a
Where not the exists (select 1
From the Performance b
Where b.i d<> Anderson, d
And b.n ame=a.n ame
And b.r esult> A.r esult)
/*
Id name result
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1 Tom 100
5 Jenny 100
(2 rows affected)
*/