CODEWITHSUNDEEP
mysqlsql
Eduardo Matos
Eduardo Matos

Reputation: 741

Fetching last rows in MySQL

I'd like to retrieve the last ocurrence of each user from the table below.

| ID | NAME | FLAG |                     CREATED_AT |
|----|------|------|--------------------------------|
|  1 | John |    1 | January, 15 2014 00:00:00+0000 |
|  2 | Mary |    1 | January, 16 2014 00:00:00+0000 |
|  3 | John |    0 | January, 17 2014 00:00:00+0000 |
|  4 | John |    1 | January, 18 2014 00:00:00+0000 |
|  5 | John |    0 | January, 19 2014 00:00:00+0000 |
|  6 | Mary |    0 | January, 20 2014 00:00:00+0000 |
|  7 | John |    1 | January, 21 2014 00:00:00+0000 |

The correct result should be (order doesn't matter): 

| NAME | FLAG |                MAX(CREATED_AT) |
|------|------|--------------------------------|
| John |    1 | January, 21 2014 00:00:00+0000 |
| Mary |    0 | January, 20 2014 00:00:00+0000 |

But my result is (flag is incorrect for Mary):

| NAME | FLAG |                MAX(CREATED_AT) |
|------|------|--------------------------------|
| John |    1 | January, 21 2014 00:00:00+0000 |
| Mary |    1 | January, 20 2014 00:00:00+0000 |

The query I'm using is this:

SELECT name, flag, max(created_at)
FROM logs
GROUP BY name

What am I missing?

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You are using a MySQL extension to group by that the documentation explicitly warns against. The extension is referring to a column in the select that is not in the group by. The value is arbitrary. See here.

Here is a way to rewrite the query:

SELECT name, substring_index(group_concat(flag order by created_at desc), ',', 1) as flag, max(created_at)
FROM logs
GROUP BY name;

Upvotes: 1

Related Questions