SQL group by to remove duplicates and sort by other fields

need:

Merge identical items of one field and sort them by another time field.

example:

At the beginning

select city from table group by city order by date desc

An error will be reported because date is not included in the aggregate function or GROUP BY clause

Then put the date into the group by:

select city from table group by city,date order by date desc

Get the result

But the results are still repeated and there is no solution

If it is not sorted by time, it will affect my subsequent operations, so I searched Baidu for a long time and finally found a solution:

Correct way to write:

select city from table group by city order by max(date) desc

Found a very magical result

Then I looked for some more information and found the magic of max():

select city,max(date) as d1 from table group by city,d1 order by d1 desc

Write it here in front and you can see the time sorting

If there is a better way, please share with us.

Supplement: Deduplication in MYSQL, the difference between DISTINCT and GROUP BY

For example, there is the following table user:

Distinct will filter out records that have duplicate fields after it.

Use distinct to return unique user names: select distinct name from user;, the result is:

Use distinct to return unique names and ids: select distinct name,id from user;, the result is:

If you write distinct name,id like this, MySQL will think that you want to filter out records with duplicates in both the name and id fields.

If the sql is written like this:

select id,distinct name from user

In this case, MySQL will report an error because distinct must be placed at the beginning of the field to be queried.

Group by can remove duplicates from one of the multiple fields to be queried:

select id,name from user group by name;

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.