0

I need to return the row with the maximum timestamp grouped by the code column but I can't seem to get it to work. I'm on MySQL 5.7. I've run queries like this before and it seemed to work in the past. Not sure what's changed or what I could be doing wrong.

mysql> CREATE TABLE test (code VARCHAR(20), timestamp DATETIME, id VARCHAR(20));

mysql> INSERT INTO test (code, timestamp, id) VALUES ('NA01M06152018','2018-06-15 21:01:00','111'),('NA01M06152018','2018-06-15 22:01:00','222'),('NA01M06152018','2018-06-15 23:01:00','333');

mysql> SELECT * FROM test;
+---------------+---------------------+------+
| code          | timestamp           | id   |
+---------------+---------------------+------+
| NA01M06152018 | 2018-06-15 21:01:00 | 111  |
| NA01M06152018 | 2018-06-15 22:01:00 | 222  |
| NA01M06152018 | 2018-06-15 23:01:00 | 333  |
+---------------+---------------------+------+

mysql> SELECT code, MAX(timestamp), id FROM test GROUP BY code;
+---------------+---------------------+------+
| code          | MAX(timestamp)      | id   |
+---------------+---------------------+------+
| NA01M06152018 | 2018-06-15 23:01:00 | 111  |
+---------------+---------------------+------+

I would expect the id to be 333 because that's the row with the max timestamp but I get 111 instead which is the row with the minimum timestamp.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Marc
  • 1,470
  • 2
  • 16
  • 25
  • https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column – someRandomSerbianGuy Jun 16 '18 at 01:31
  • If you were to run `SELECT code, MAX(timestamp), MIN(timestamp), id FROM test GROUP BY code;` which id would you expect it to return? – Bill Karwin Jun 16 '18 at 01:47
  • I added the tag `greatest-n-per-group`. This type of question has been answered many times on Stack Overflow. Follow the tag to find many solutions. – Bill Karwin Jun 16 '18 at 01:48

0 Answers0