How do I group 2 columns with a max value in a third column using MySQL?

Question

With this table....

CREATE TABLE test
(
  id INT,
  date VARCHAR(50),
  g1 VARCHAR(50),
  stats INT
 );
 
 INSERT INTO test VALUES (1,'2002-01-01','a',2);
 INSERT INTO test VALUES (2,'2002-01-02','a',3);
 INSERT INTO test VALUES (3,'2002-01-03','a',4);
 INSERT INTO test VALUES (4,'2002-01-01','b',9);
 INSERT INTO test VALUES (5,'2002-01-02','b',8);
 INSERT INTO test VALUES (6,'2002-01-03','b',7);
 INSERT INTO test VALUES (7,'2002-01-01','c',6);
 INSERT INTO test VALUES (8,'2002-01-02','c',9);
 INSERT INTO test VALUES (9,'2002-01-03','c',5);

I would like to get the following result.

date	g1	MAX(stats)
2002-01-03	a	4
2002-01-01	b	9
2002-01-02	c	9

I have this query. But adding the date column has been challenging.

SELECT g1, MAX(stats)
FROM test
GROUP BY g1

Please help. Thank you.

Answer 1

If your mysql version suuport ROW_NUMBER window function you can try this.

SELECT *
FROM (
 SELECT *,ROW_NUMBER() OVER(PARTITION BY g1 ORDER BY stats DESC) rn
 FROM test
) t1
WHERE rn = 1

or EXISTS subquery

SELECT *
FROM test t1
WHERE EXISTS (
    SELECT 1
    FROM test tt
    WHERE tt.g1 = t1.g1
    HAVING MAX(tt.stats) = t1.stats
)

sqlfiddle