1

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??

  • 1
    Loosely related to your issue: do you know there is a time type that you could use instead of the varchar(20)? – ypercubeᵀᴹ Mar 27 '15 at 8:33
1

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

  • There is a chance that your query would show false positives (different ids, datetime in one id identical to the max(datetime) in the other). And since subqueries with in usually are not very efficient in mysql, I'll edit your query. You can of course revert if you don't like my changes ;) – ypercubeᵀᴹ Mar 27 '15 at 15:36
  • OK - I'd be grateful to see how it could be improved. I did implicitly assume that such duplicates would not be an issue - won't my subquery simply pick the MAX(posted_datetime) per user regardless of the datetime for one user being the MAX(posted_datetime) for another? – Vérace Mar 27 '15 at 15:39
  • Keeping the previous IN syntax would be possible with WHERE (id, posted_datetime) IN (SELECT id, MAX(posted_datetime) ...) but as I said (several versions of) the mysql optimizer cannot fully optimize queries with where x in (select ...) – ypercubeᵀᴹ Mar 27 '15 at 15:42
  • If you meant to write a correlated subuery, it would have to be: WHERE posted_datetime in (SELECT MAX(posted_datetime) FROM example WHERE id = e.id); – ypercubeᵀᴹ Mar 27 '15 at 15:46
  • I'll edit the SQLFiddle - later though. I am actually paid to do other stuff! :-) – Vérace Mar 27 '15 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.