Syntax error How to group by column name and ensure the query retrieves the last update in MySQL?

How to group by column name and ensure the query retrieves the last update in MySQL?

Let us first create a table −

mysql> create table DemoTable621 (UserName varchar(100),UserEmailId varchar(100),UserLastPost datetime);
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable621 values('John','John@gmail.com','2019-04-10 11:01:10');
Query OK, 1 row affected (0.47 sec)
mysql> insert into DemoTable621 values('John','John@gmail.com','2019-07-14 13:07:10');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable621;

This will produce the following output −

+----------+----------------+---------------------+
| UserName | UserEmailId    | UserLastPost        |
+----------+----------------+---------------------+
| John     | John@gmail.com | 2019-04-10 11:01:10 |
| John     | John@gmail.com | 2019-07-14 13:07:10 |
+----------+----------------+---------------------+
2 rows in set (0.00 sec)

Following is the query to group by column name and ensure the query retrieves the last update −

mysql> select UserName,UserEmailId,max(UserLastPost) from DemoTable621 group by UserName,UserEmailId;

This will produce the following output −

+----------+----------------+---------------------+
| UserName | UserEmailId    | max(UserLastPost)   |
+----------+----------------+---------------------+
| John     | John@gmail.com | 2019-07-14 13:07:10 |
+----------+----------------+---------------------+
1 row in set (0.19 sec)
Updated on: 2019-08-23T07:12:30+05:30

102 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements