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

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.

CodePudding user response：

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

CodePudding user response：

An extra join should work, I have used the CTE style here but you can use a subquery too.

WITH temp as (SELECT g1, MAX(stats) as stats
FROM test
GROUP BY g1)
SELECT test.date, temp.g1, temp.stats FROM test INNER JOIN temp ON temp.stats = test.stats AND temp.g1 = test.g1;

https://www.db-fiddle.com/f/cvq37zrwEtdHNnQTbJydHK/0