MySQL: GROUP_CONCAT with an ORDER BY COUNT?

Question

Is this possible ?

Let's say I have a list of addresses, with a lot of duplicate entries. I need to filter out all the duplicates, because some addresses have slightly different names but the same postalcode and telephone number.

First I do a GROUP BY on postalcode and telephone.

SELECT name, address, postalcode, place, phone
FROM addresses
GROUP BY postalcode, phone

But then I get random names. I would like to get the best name, that is, the name with the most entries per postalcode/phone.

So I thought of the following. Here I use the SUBSTRING_INDEX function to only get the first item in the group_concat (there are no names with the string '~~' in it):

SELECT SUBSTRING_INDEX(
         GROUP_CONCAT(DISTINCT name ORDER BY COUNT(name) DESC SEPARATOR '~~')
       , '~~', 1),
       address,
       postalcode,
       place,
       phone
FROM addresses
GROUP BY postalcode, telephone

but I get an 'invalid use of group function'.

How do I get the GROUP_CONCAT to order by the number of times the name occurs ?

And what should happen with address and place? They might have different values too which might not be related to the returned name. — Martin Smith
– Martin Smith, Commented Aug 18, 2011 at 0:16
Well, I use a postalcode table to check address/place from postalcode, so it's basically names I need to filter. (In Holland we have a different postalcode for each street) — Dylan
– Dylan, Commented Aug 18, 2011 at 0:19

Dylan · Accepted Answer · 2011-08-18 03:24:12Z

1

Found a solution myself, with a subquery:

SELECT 
  SUBSTRING_INDEX(
    GROUP_CONCAT(DISTINCT name ORDER BY CountName DESC SEPARATOR '||')
  , '||', 1),
  address,
  postalcode,
  place,
  phone
FROM (

  SELECT name, address, postalcode, place, phone , COUNT(name) AS CountName
  FROM addresses
  GROUP BY name, postalcode, phone
  ORDER BY COUNT(name) DESC

) as a
GROUP BY postalcode, phone

I wonder if it can be done without a subquery.

edited Aug 18, 2011 at 3:24

answered Aug 18, 2011 at 1:30

Dylan

9,40322 gold badges101 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephane Gosselin Over a year ago

That, to me would be a single query. You have a sub-select in there but it is still one query, it seems.

Collectives™ on Stack Overflow

MySQL: GROUP_CONCAT with an ORDER BY COUNT?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related