No duplicates in rows after initial match

Question

I'm actually not even sure if it's possible, but I'm just trying my luck as I've tried around with stuff for a bit, but can't really grasp it.

Image of my table

What I want: I want the gpID once, and I want it to be at the date it happened first. For example, gpID 1 and 2 were finished by pID 1 on the 28th of August. I don't want the gpID 1 and 2 to be included in the result for the other dates. I tried DISTINCT, which at least gets rid of the duplicates in each row, however, it won't really solve my problem.

SELECT
    GROUP_CONCAT( DISTINCT gpID) AS gpID,
    pID,
    score,
    FROM_UNIXTIME(DATE, '%Y %M %D') AS DATE
FROM
    points
where pID = 1
group by FROM_UNIXTIME(date, '%Y %M %D')

which results in


gpID    pID         score   DATE    
1,2,3     1         125220  2019 August 28th    
1,2,3,4   1         30000   2019 September 16th

Is there any way to get rid of gpID 1,2,3 for all dates after 28th of August?

It's probably partially a duplicate of [this](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column). But your question is too unclear to be answered or even marked as duplicate. — Paul Spiegel, Oct 01 '19 at 17:10

Barmar · Accepted Answer · 2019-10-01T19:39:05.937

0

Write a subquery that gets the first date for each gpID. Then group these by date.

SELECT DATE(FROM_UNIXTIME(mindate)) AS date , GROUP_CONCAT(gpID) AS gpID
FROM (
    SELECT gpID, MIN(date) AS mindate
    FROM points
    GROUP BY gpID
) AS x
GROUP BY date

edited Oct 01 '19 at 19:39

answered Oct 01 '19 at 17:10

Barmar

741,623
53
500
612

Thanks, that did the trick, would it be possible to include/group the pID as well? I know I can add "where pID = 1", but I'd rather see all pIDs, instead of selecting only specific IDs. – wtp2019 Oct 01 '19 at 19:36
Include it in what way? The pID can be different for different `gpID` on the same date. – Barmar Oct 01 '19 at 19:38
Basically, I'd like pID to be included in the resulting table. All of them, currently I've to add "where pID = 2" to the query if I want to see the results for player 2. – wtp2019 Oct 01 '19 at 19:59
You still haven't explained how it should be included if there are multiple pID values on the same date. Do they need to be separate rows? – Barmar Oct 01 '19 at 20:15
Can the same `gpID` have muliple `pID`? – Barmar Oct 01 '19 at 20:16

No duplicates in rows after initial match

1 Answers1