Home >Database >Mysql Tutorial >Invalid alternative for using group by and order by at the same time in mysql
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.
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 barCode
4565789
as an example.
SELECTbarCode, priCommodityID, createDateFROMtb_history_versionWHEREbarCode = '4565789'ORDER BYcreateDate DESC;
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
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.
group by
and order by
have no effect if used at the same time. You can use group_concat
and groub by
replacement. Field sorting can be implemented within group_concat
.
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!