How to specify order by which grouping is performed

Question

Having table msg like this:

id, msgNo, frag
1, 1, AAA
2, 1, BBB
3, 1, CCC
4, 2, Hello

I can get such result:

msgNo, mesg
1, AAABBBCCC
2, Hello

using:

SELECT msgNo, STRING_AGG(CONVERT(NVARCHAR(MAX), frag), '') AS mesg
FROM msg
GROUP BY msgNo

Question is, how can I get this?:

msgNo, mesg
1, CCCBBBAAA
2, Hello

meaning, that I concatenate strings in descending id order. If someone can give me an explanation for both MySQL and Sql Server that would be awesome.

For MySQL, see GROUP_CONCAT – Strawberry Mar 10 '20 at 12:13 — Strawberry, Mar 10 '20 at 12:13

score 2 · Answer 1 · answered Mar 10 '20 at 12:15

I didnt read whole docs...

For SQL Server:

SELECT msgNo, STRING_AGG(CONVERT(NVARCHAR(MAX), frag), '') WITHIN GROUP (ORDER BY id ASC) AS mesg
    FROM msg
    GROUP BY msgNo

GROUP_CONCAT has similar functionality.

score 1 · Accepted Answer · answered Mar 10 '20 at 12:41

1

try this one

SELECT msgNo, GROUP_CONCAT(frg ORDER BY frg DESC SEPARATOR '') 
FROM (
   SELECT 1 as id, 1 as msgNo, 'AAA' as frg
   UNION SELECT 2, 1, 'BBB'
   UNION SELECT 3, 1, 'CCC'
   UNION SELECT 4, 2, 'Hello') tbl 
GROUP BY msgNo

Your query will be

SELECT msgNo, GROUP_CONCAT(frag ORDER BY frag DESC SEPARATOR '') AS mesg
FROM msg
GROUP BY msgNo

answered Mar 10 '20 at 12:41

gilbertdim

357
2
11

similar post https://stackoverflow.com/questions/995373/mysql-sort-group-concat-values – gilbertdim Mar 10 '20 at 12:43

How to specify order by which grouping is performed

2 Answers2