Invalid alternative for using group by and order by at the same time in mysql

Preface

In the past year, due to work needs, most of the NoSql databases have been used, and I feel more and more unfamiliar with relational databases. A combination of group by and order The murder caused by by was born from this. Make a note here for emergencies.

Requirements

First, take a look at the overall table structure.

Now find the latest data in each barCode.

Because there is too much data, the effect is not very good. Let’s take a data with barCode4565789 as an example.

SELECTbarCode,
    priCommodityID,
    createDateFROMtb_history_versionWHEREbarCode = '4565789'ORDER BYcreateDate DESC;

Trial and error

Because I haven’t written sql for a long time. So I first thought of using group by and order by combined query.

SELECTbarCode,
    priCommodityID,
    createDateFROMtb_history_versionWHEREbarCode = '4565789'GROUP BYbarCodeORDER BYcreateDate DESC;

The results are as follows:

You can see that this is not the result we want, order by has no effect.
Next, let’s try to combine the two using subqueries. Sort first and then group

SELECT*FROM(SELECTbarCode,
            priCommodityID,
            createDateFROMtb_history_versionWHEREbarCode = '4565789'ORDER BYcreateDate DESC) AS AGROUP BYA.barCode;

The result is still disappointing

Solution

I have tried the above two methods, Although the result is sad, the work must continue. So I searched for various information online to see if I could solve the problem in other ways. I accidentally saw that group_concat can achieve group sorting, so I gave it a try

SELECTbarCode,
    GROUP_CONCAT(
        priCommodityIDORDER BYcreateDate DESC) AS priCommodityID,
    GROUP_CONCAT(
        createDateORDER BYcreateDate DESC) AS createDateFROMtb_history_versionWHEREbarCode = '4565789';

The results are as follows

You can see the order No problem anymore, but all the data is spliced ​​together. Further processing of intercepting characters is required

SELECTbarCode,
    SUBSTRING_INDEX(
        group_concat(
            priCommodityIDORDER BYcreateDate DESC),',',1) AS priCommodityID,
    SUBSTRING_INDEX(
        group_concat(
            createDateORDER BYcreateDate DESC),',',1) AS createDateFROMtb_history_versionWHEREbarCode = '4565789'GROUP BYbarCode;

ok! At this point, we find that our initial needs have been achieved.

Summary

group by and order by have no effect if used at the same time. You can use group_concat and groub byreplacement. Field sorting can be implemented within group_concat.

Reference article

Initial address

http://www.devzxd.top/2017/05/27/mysql-groupby -orderby.html

The above is the detailed content of Invalid alternative for using group by and order by at the same time in mysql. For more information, please follow other related articles on the PHP Chinese website!