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
You can use the technique described in:
http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/
to mimic:
row_number() over (partition by ... order by ...)
In your case that would be something like:
SELECT user_id, comment, row_number
FROM (
SELECT @row_number:=CASE WHEN @user_id=user_id
THEN @row_number+1
ELSE 1
END AS row_number
, @user_id:=user_id AS user_id
, comment
FROM t
, (SELECT @row_number:=0,@user_id:='') AS u
ORDER BY user_id, comment
) as v
WHERE row_number <= 2;
Best Answer
On MySQL 8.0 one solution is using ROW_NUMBER function in this way:
Notice I've used
Grp
instead ofGroup
, because it's a reserved word.There is no way to get Julia instead of Lalo, unless you add another column to the ORDER BY clause.
db<>fiddle here