Mysql – Unable to get the latest value added to Mysql table based on posted time

MySQLmysql-5.5

SELECT * FROM discussion_comments GROUP BY disc_id ORDER BY posted_date DESC

I have table example like given below:

CREATE TABLE example
(
id int(11),
cname varchar(11),
posted_date date,
posted_time varchar(20)
);

with values like:

INSERT INTO example
VALUES (1,'abc','2015-03-26','04:25 PM');

INSERT INTO example
VALUES (1,'def','2015-03-27','04:30 PM');

INSERT INTO example
VALUES (2,'ghi','2015-03-11','02:25 AM');

INSERT INTO example
VALUES (2,'jkl','2015-03-15','12:25 PM');

and I am trying to get only the latest value added to the table for an id based on posted_date & posted_time fields.

The result I am trying to achieve is:

(1,'def','2015-03-27','04:30 PM')
(2,'jkl','2015-03-15','12:25 PM')

The query I tried is as follows:

SELECT * FROM `example GROUP BY id ORDER BY posted_date DESC

I am not getting the desired result. Where did I go wrong??

Best Answer

Your first HUGE mistake (as @ypercube pointed out) is not using a DATETIME type for a date-time type variable. TIMEs are not VARCHARs and doing this will mess up your queries, make your app non-portable and will confuse the optimiser.

The other HUGE problem is MySQL. It doesn't perform this sort of query properly. Your query is ambiguous - PostgreSQL for example will throw an error if you try and run such a query.

For example

mysql> SELECT id, MAX(posted_date) FROM example GROUP BY id;
+------+------------------+
| id   | MAX(posted_date) |
+------+------------------+
|    1 | 2015-03-27       |
|    2 | 2015-03-15       |
+------+------------------+
2 rows in set (0.00 sec)

mysql>

is unambiguous and gives the correct answer.But, because you have tried to use a SELECT * FROM example, the MySQL engine doesn't know which values to return and (stupidly enough) appears (at least in the several examples of this problem that I have seen) to return the correct unambiguous values, but the other are random - so you could get id = 1 (correct) with cname = 'ghi' (incorrect).

Check out MySQL's SQL modes - there's one (ONLY_FULL_GROUP_BY) that disallows this behaviour. It should be switched on at ALL times.

To get your query to work properly, for starters, use a DATETIME type (makes the subquery below easier) and then do a subquery for cname where your DATETIME value matches that for the correct cname. HTH,

[EDIT]

Using this schema and data, your query becomes relatively easy.

CREATE TABLE example
(
id int(11),
cname varchar(10),
posted_datetime datetime
); 


INSERT INTO example
VALUES (1, 'abc', '2015-03-26 04:25');

INSERT INTO example
VALUES (1, 'def', '2015-03-27 16:30');

INSERT INTO example
VALUES (2, 'ghi', '2015-03-11 02:25');

INSERT INTO example
VALUES (2, 'jkl', '2015-03-15 12:25');

And your working query is:

SELECT e.id, e.cname, e.posted_datetime 
FROM example e
  JOIN
  (
    SELECT id, MAX(posted_datetime) AS posted_datetime
    FROM example 
    GROUP BY id
  ) m
  ON  m.id = e.id 
  AND m.posted_datetime = e.posted_datetime ;

or check it out here: SQLfiddle

Related Question