Solved

How to limit group_concat to 3?

Posted on 2010-08-12
10
3,088 Views
Last Modified: 2012-05-10
I want to do a group_concat that will only concat a maximum of 3 values - how can I do that?

Current code is below

Thanks for any help!
SELECT ml1.masterdeedname, 
group_concat(concat(ml1.site_addr_1,", ",ml1.site_addr_2) order by ml1.new_owner_date desc separator "\r") as PropertyAddresses 
FROM `mailinglists`.`cashbuyers_0001` as ml1
group by ml1.masterdeedname

Open in new window

0
Comment
Question by:SAbboushi
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 33426852
Not sure about the rest, but mysql you can use LIMIT
Limit 3

Is that what you mean?

as below..

update contacts.contacts as c join

(SELECT ml1.masterdeedname,
group_concat(concat(ml1.site_addr_1,", ",ml1.site_addr_2) order by ml1.new_owner_date desc separator "\r") as PropertyAddresses
FROM `mailinglists`.`cashbuyers_0001` as ml1
group by ml1.masterdeedname) as ml
limit 3

on mastername=masterdeedname

set c.PropertyAddresses = ml.propertyaddresses
 
0
 

Author Comment

by:SAbboushi
ID: 33426863
PS - I am looking for a way to do this in the QUERY (i.e. in the subquery), not in the UPDATE / set
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33426865
I'm assuming you tried LIMIT, its prob not that simple, huh  ;)
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:SAbboushi
ID: 33426874
Thanks - I believe that will limit the result to 3 row, each of which will have a group_concat column with no limit; I am looking to limit the number of values within the group_concat column of EACH row.  Please let me know if I have misunderstood
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 33426898

SELECT ml1.masterdeedname, 
group_concat(concat(ml1.site_addr_1,", ",ml1.site_addr_2) order by ml1.new_owner_date desc separator "\r") as PropertyAddresses 
FROM `mailinglists`.`cashbuyers_0001` as ml1
WHERE (SELECT COUNT(1) FROM `mailinglists`.`cashbuyers_0001`
         WHERE masterdeedname=ml1.masterdeedname
           AND new_owner_date>ml1.new_owner_date
      )<3
group by ml1.masterdeedname

Open in new window

0
 

Author Comment

by:SAbboushi
ID: 33426927
Thanks Thomasian - tried that but it did not limit the number of values in the group_concat...
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 33426934
Did it at least lessen the number of values in the group_concat?

Is it possible to have records with the same new_owner_date? If so, how do you determine which one to choose?
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 33426939
Just to be sure, I added an alias to the table in the subquery.
SELECT ml1.masterdeedname, 
group_concat(concat(ml1.site_addr_1,", ",ml1.site_addr_2) order by ml1.new_owner_date desc separator "\r") as PropertyAddresses 
FROM `mailinglists`.`cashbuyers_0001` as ml1
WHERE (SELECT COUNT(1) FROM `mailinglists`.`cashbuyers_0001` as ml2
         WHERE ml2.masterdeedname=ml1.masterdeedname
           AND ml2.new_owner_date>ml1.new_owner_date
      )<3
group by ml1.masterdeedname

Open in new window

0
 
LVL 21

Accepted Solution

by:
theGhost_k8 earned 500 total points
ID: 33427068
string function: substring_index will help:

SELECT ml1.masterdeedname,
substring_index(group_concat(concat(ml1.site_addr_1,", ",ml1.site_addr_2) order by ml1.new_owner_date desc separator "\r"),"\r",3) as PropertyAddresses
FROM `mailinglists`.`cashbuyers_0001` as ml1
group by ml1.masterdeedname
0
 

Author Comment

by:SAbboushi
ID: 33603753
Thomasian - thanks for your efforts - but your code did not work for me.

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question