Solved

How to limit group_concat to 3?

Posted on 2010-08-12
10
3,413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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:
K V 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

626 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