How to use MySQL group by and order by together

Suppose there is a table: reward (reward table), the table structure is as follows:

CREATE TABLE test.reward (
 id int(11) NOT NULL AUTO_INCREMENT,
 uid int(11) NOT NULL COMMENT 'user uid',
 money decimal(10, 2) NOT NULL COMMENT 'Reward amount',
 datatime datetime NOT NULL COMMENT 'time',
 PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Reward table';

The data in the table are as follows:

Now we need to query the highest reward received by each person and sort them from largest to smallest:

If you query directly:

SELECT id, uid, money, datatime FROM reward GROUP BY uid ORDER BY money DESC;

The following results are obtained:

We did not get the results we needed. This is because when group by and order by are used together, group by is used first, and the first piece of data after grouping is taken out. Therefore, the subsequent order by sorting is based on the first piece of data taken out, but the first piece of data is not necessarily the largest piece of data in the group.

Method 1:

In this case we can sort first, then group, and use a subquery.

SELECT
 r.id,
 r.uid,
 r.money,
 r.datatime
FROM (SELECT
  id,
  uid,
  money,
  datatime
 FROM reward
 ORDER BY money DESC) r
GROUP BY r.uid
ORDER BY r.money DESC;

Method 2:

If you don't need to get the entire record, you can use max() min()

SELECT id, uid, money, datatime, MAX(money) FROM reward GROUP BY uid ORDER BY MAX(money) DESC;

The result is:

You may have discovered that the money field and max(money) field of the records obtained using max() are inconsistent. This is because only the maximum value of the uid is retrieved here, but the entire record corresponding to the maximum value is not retrieved.

If you need to get the entire record, you cannot use this method and can use a subquery.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.