CODEWITHSUNDEEP
mysqlsql
user1475692
user1475692

Reputation: 95

MySQL, understanding SQL query behavior

I have a simple MySQL table:

|   id   |   sid   |    date    | 
+--------+---------+------------+
| 1      | 1       | 2013-12-01 |
+--------+---------+------------+
| 3      | 2       | 2013-12-17 |
+--------+---------+------------+
| 4      | 1       | 2013-12-17 |
+--------+---------+------------+
| 5      | 1       | 2013-12-18 |
+--------+---------+------------+

I need group this table by sid field and get records with max id for each sid with correct date. I try below code:

SELECT MAX(id), date FROM my_table GROUP BY sid

But the date field is incorrect, for example I get date 2013-12-01 with id 5 as a result. What am I doing wrong ?

Upvotes: 0

Views: 44

Answers (2)

Sebas
Sebas

Reputation: 21522

The way standard SQL is defined (at least up to ansi 1992, others will correct me), any field of your SELECT clause must be included in your group by condition. [Mysql allows you to not do so, but that is why it is confusing: the results are not as you expect]

Your query should then be:

SELECT MAX(id), date FROM my_table GROUP BY sid, date

But in this case, clearly this is not what you want. Your requirement is to get the date corresponding to the MAX(id) for each sId.

You have to isolate each part of the algorithm in different queries.

1 - get the max id for each sid:

SELECT MAX(id) AS id, sid FROM my_table GROUP BY sid

2 - get the date corresponding to the max of ids for each sid:

SELECT date FROM my_table WHERE sid = X AND id = Y

3 - join these 2 queries using an INNER JOIN or more shortly, a JOIN:

SELECT m.sid, m.id, m.date
FROM my_table m
    JOIN (SELECT MAX(id) AS id, sid FROM my_table GROUP BY sid) t
        ON t.sid = m.sid AND m.id = t.id

Upvotes: 2

xdazz
xdazz

Reputation: 160833

You need a join:

SELECT a.id, a.date
FROM foo a
INNER JOIN (SELECT MAX(id) as max_id FROM foo GROUP BY sid) b ON a.id = b.max_id

Upvotes: 2

Related Questions