CODEWITHSUNDEEP
mysqlsql
Pastor Bones
Pastor Bones

Reputation: 7371

MySQL query returning unexpected results

I have 2 tables, one containing information on a city (in a game) and another containing scores as recorded throughout periods of the day. Table examples have been simplified.

CREATE TABLE cities(
  cid INT(100),
  name VARCHAR(100),
  updatedAt DATETIME
);
CREATE TABLE cl_scores(
  cid INT(100),
  score INT(255),
  updatedAt DATETIME
);

I'm trying to gather a list of scores for a particular city. The list should only contain the MAX(updatedAt) date and score for every day. Here is what I have so far:

SELECT a.cid, b.name, a.score, a.updatedAt FROM ci_scores AS a 
JOIN cities AS b ON a.cid = b.cid 
JOIN (SELECT MAX(updatedAt) AS maxUpdatedAt FROM ci_scores 
GROUP BY DATE(updatedAt)) AS L ON DATE(L.maxUpdatedAt) = DATE(a.updatedAt) 
WHERE a.cid = 10158241 ORDER BY a.updatedAt ASC;

However, it isn't returning a single entry for each day. It is in fact returning every row in the ci_scores table and simply adding the name field as shown below:

+----------+---------+-------+---------------------+
| cid      | name    | score | updatedAt           |
+----------+---------+-------+---------------------+
| 10158241 | Genesis |  3087 | 2012-03-13 04:04:03 |
| 10158241 | Genesis |  3207 | 2012-03-13 17:48:56 |
| 10158241 | Genesis |  3255 | 2012-03-14 00:44:11 |
| 10158241 | Genesis |  3262 | 2012-03-14 10:21:05 |
| 10158241 | Genesis |  3262 | 2012-03-14 13:42:42 |
+----------+---------+-------+---------------------+

What am I doing wrong in my query???

Solutions Tried

SELECT a.cid, b.name, a.score, a.updatedAt FROM ci_scores AS a 
JOIN cities AS b ON a.cid = b.cid 
JOIN (SELECT MAX(updatedAt) AS maxUpdatedAt FROM ci_scores 
GROUP BY DATE(updatedAt) LIMIT 1) AS L ON L.maxUpdatedAt = a.updatedAt
WHERE a.cid = 10158241 ORDER BY a.updatedAt ASC;

This did not work, removing the DATE() = DATE() makes it return 0 records as the times are different. If I add that back in it returns:

+----------+---------+-------+---------------------+
| cid      | name    | score | updatedAt           |
+----------+---------+-------+---------------------+
| 10158241 | Genesis |  3087 | 2012-03-13 04:04:03 |
| 10158241 | Genesis |  3207 | 2012-03-13 17:48:56 |
+----------+---------+-------+---------------------+

This is not correct, I need 1 record for each day and that record being the latest entered on that day.

Upvotes: 0

Views: 151

Answers (3)

Simon Wang
Simon Wang

Reputation: 2963

I think you should join your subset by updateAT without DATE function

SELECT a.cid, b.name, a.score, a.updatedAt FROM ci_scores AS a 
JOIN cities AS b ON a.cid = b.cid 
JOIN (SELECT MAX(updatedAt) AS maxUpdatedAt FROM ci_scores 
GROUP BY DATE(updatedAt)) AS L ON L.maxUpdatedAt = a.updatedAt
WHERE a.cid = 10158241 ORDER BY a.updatedAt ASC;

UPDATE: There is another error that in the sub query you need to group by the city id as well so that you can get the correct max date:

SELECT a.cid, b.name, a.score, a.updatedAt FROM ci_scores AS a 
JOIN cities AS b ON a.cid = b.cid 
JOIN (SELECT MAX(updatedAt), cid AS maxUpdatedAt FROM ci_scores 
GROUP BY DATE(updatedAt), cid) AS L ON L.maxUpdatedAt = a.updatedAt AND L.cid = a.cid
WHERE a.cid = 10158241 ORDER BY a.updatedAt ASC;

Upvotes: 2

Yahia
Yahia

Reputation: 70369

try

SELECT DISTINCT A.CID, C.NAME, A.updatedAt, A.SCORE FROM cl_scores A
INNER JOIN
(
SELECT D.CID, D.SD, MAX ( S.updatedAt ) MD FROM cl_scores S
INNER JOIN (SELECT DISTINCT S.CID, DATE ( S.updatedAt ) SD FROM cl_scores S WHERE S.CID = 10158241) D ON D.CID = S.CID AND D.SD = DATE ( S.updatedAt )
GROUP BY D.CID, D.SD
) B ON B.CID = A.CID AND B.MD = A.updatedAt
INNER JOIN cities C ON C.CID = A.CID

The above gives you for the CID = 10158241 per day (only days present in the table!) the row with the max updatedAt and its respective score... BEWARE that if you have two rows with the EXACT same updatedAt (down to the second...) it might give you more than one row...

Upvotes: 0

Teja
Teja

Reputation: 13544

SELECT a.cid, b.name, a.score, a.updatedAt FROM ci_scores AS a 
JOIN cities AS b ON a.cid = b.cid 
JOIN (SELECT MAX(updatedAt) AS maxUpdatedAt FROM ci_scores 
GROUP BY DATE(updatedAt) LIMIT 1) AS L ON L.maxUpdatedAt = a.updatedAt
WHERE a.cid = 10158241 ORDER BY a.updatedAt ASC;

Upvotes: 1

Related Questions