MySQL GROUP_CONCAT DISTINCT ordering anomaly

Question

I've been reading about this for the past day (even here), and have not found a suitable resource, so I'm popping it out there again :)

Check out these two queries:

SELECT DISTINCT transactions.StoreNumber FROM transactions WHERE PersonID=2 ORDER BY transactions.transactionID DESC;

and

SELECT GROUP_CONCAT(DISTINCT transactions.StoreNumber ORDER BY transactions.transactionID DESC SEPARATOR ',') FROM transactions WHERE PersonID=2 ORDER BY transactions.transactionID DESC;

From everything I've read I would expect the two queries to return the same results, with the second set grouped into CSV. They're not though.

Result set for query 1 (picture each value in its own row, formatting results here is cumbersome):

'611' '345' '340' '310' '327' '323' '362' '360' '330' '379' '356' '367' '375' '306' '354' '389' '343' '346' '357' '733' '370' '347' '703' '355' '341' '342' '358' '351' '319' '365' '372' '368' '353' '363' '349' '369' '336' '364' '202' '366' '416' '731'

Result Set for query 2: 611,379,375,389,703,355,351,372,368,362,342,365,353,341,733,347,336,319,354,306,345,364,202,358,370,343,366,349,356,367,369,416,323,346,731,360,363,330,310,357,340,327

If I remove the DISTINCT clause, the results line up.

Can anyone point out what I'm doing wrong with the difference between the queries above?

The fact that removing DISTINCT from each query returns the same result indicates that DISTINCT is problematic within GROUP_CONCAT. Performing a GROUP BY outside the GROUP_CONCAT causes multiple rows to be returned, which isn't what I'm after.

Any ideas on how I can get a GROUP_CONCAT DISTINCT list of StoreNumber, in order of TransactionID DESC?

Thanks all

Gordon Linoff Gordon Linoff · Accepted Answer · 2013-04-19T02:53:14

Consider your first query:

SELECT DISTINCT transactions.StoreNumber
FROM transactions
WHERE PersonID=2
ORDER BY transactions.transactionID DESC;

This is equivalent to:

SELECT transactions.StoreNumber
FROM transactions
WHERE PersonID=2
group by transactions.StoreNumber
ORDER BY transactions.transactionID DESC;

You are ordering by something that is not in the select list. So, MySQL chooses an arbitrary transactionid for each store number. This may differ from one execution to another.

I believe the same thing is happening in the group_concat(). The issue is that the arbitrary number chosen is different for each one.

If you want consistency, consider these two queries:

SELECT transactions.StoreNumber
FROM transactions
WHERE PersonID=2
group by transactions.StoreNumber
ORDER BY min(transactions.transactionID) DESC;

and:

SELECT GROUP_CONCAT(DISTINCT t.StoreNumber ORDER BY t.mintransactionID DESC SEPARATOR ',')
FROM (select t.StoreNumber, min(TransactionId) as minTransactionId
      from transactions t
      WHERE PersonID=2.transactionID
      group by t.StoreNumber
     ) t

These should produce the same results.

Before you complain too loudly about MySQL, any other database would return an error on the first query, because, when using select distinct, you can only order by columns in the select list (or expressions composed of them).

MySQL GROUP_CONCAT DISTINCT ordering anomaly

1 Answers