How to clean the duplicate using this code?

Question

I need to get the fastest players who reach certain milestone with minimum total games in the following simplified table:

create table eg(
kills integer, 
matchid integer,
playername varchar(30)
date date
)

INSERT INTO eg (kills,matchid,playername,date) values(12,1,'niko','2015-01-01');
INSERT INTO eg (kills,matchid,playername,date) values(13,2,'niko','2015-01-02');
INSERT INTO eg (kills,matchid,playername,date) values(5,3,'device','2015-01-03');
INSERT INTO eg (kills,matchid,playername,date) values(6,4,'niko','2015-01-04');
INSERT INTO eg (kills,matchid,playername,date) values(5,5,'device','2015-01-05');
INSERT INTO eg (kills,matchid,playername,date) values(5,3,'device','2015-01-06');
INSERT INTO eg (kills,matchid,playername,date) values(5,3,'device','2015-01-07');

but it caused duplicate.

The query i used is as followed:

select distinct(playername),sum_value,game_played
from(
select playername,
sum(kills) over (partition by playername order by date asc, matchid asc) as sum_value,
count(*) over (partition by playername order by date asc, matchid asc) as game_played
from eg) as so
where sum_value>15
order by game_played asc

the result I ideally want is the below

playname  sum_value game_played
niko          25          2
device        20          4

but what i got is:

playname  sum_value game_played
niko          25          2
niko          31          3
device        20          4

So I would prefer a modification to the query that removes dublicate names and only give me the first instance of the player name.

can someone help me with this? Thanks

The parentheses suggest that you think `distinct` applies to a particular column. It applies to the entire `SELECT` list. — Barmar, May 23 '19 at 18:35
Why should it return `niko 25 2` and not `niko 31 3` in the desired result? — Barmar, May 23 '19 at 18:37
The window function means you want a running total, why should it only return one row for each player? — Barmar, May 23 '19 at 18:39
oh i mean i need to get the player who is the fastest to reach the milestone. so niko 25 2 but not niko 31 3 — kangt, May 23 '19 at 18:42
*"The parentheses suggest that you think distinct applies to a particular column. It applies to the entire SELECT list"* @Barmar sometimes i wished `DISTINCT ON(column), *` (PostgreSQL syntax) is standard SQL and also supported by MySQL, i wrote a [emulation/simulation](https://stackoverflow.com/questions/53868870/how-to-use-distinct-on-with-mysql-using-activerecord/53869691#53869691) once for MySQL — Raymond Nijland, May 23 '19 at 18:42

score 1 · Accepted Answer · answered May 23 '19 at 18:47

You can use a window function on the result of the subquery to get the first row for each player.

select playername,sum_value,game_played
from (
    SELECT *, 
            ROW_NUMBER() OVER (PARTITION BY playername ORDER BY game_played) `rank`
    from(
        select playername,
            sum(kills) over (partition by playername order by date asc, matchid asc) as sum_value,
            count(*) over (partition by playername order by date asc, matchid asc) as game_played
        from eg) as so
    where sum_value>15) AS t1
WHERE `rank` = 1
order by game_played asc

DEMO

How to clean the duplicate using this code?

1 Answers1