[Solved] mysql return date by top position

by


Consider the following…

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,userid INT NOT NULL 
,date DATE NOT NULL
,score INT NOT NULL
,UNIQUE(userid,date)
);

INSERT INTO my_table (userid,date,score) VALUES
(1,'2017-09-30',1),
(1,'2017-10-01',1),
(2,'2017-10-01',2),
(1,'2017-10-02',2),
(2,'2017-10-02',2),
(3,'2017-10-02',1);

SELECT x.*
     , COUNT(DISTINCT y.score) rank 
  FROM my_table x 
  JOIN my_table y 
    ON y.date = x.date 
   AND y.score >= x.score 
 GROUP 
    BY x.date,x.userid 
HAVING rank = 1 AND userid = 1;
+--------+------------+-------+------+
| userid | date       | score | rank |
+--------+------------+-------+------+
|      1 | 2017-09-30 |     1 |    1 |
|      1 | 2017-10-02 |     2 |    1 |
+--------+------------+-------+------+

or

SELECT x.*
  FROM my_table x 
  JOIN my_table y 
    ON y.date = x.date 
   AND y.score >= x.score 
 GROUP 
    BY x.date,x.userid 
HAVING userid = 1
   AND COUNT(DISTINCT y.score) = 1;

4

solved mysql return date by top position