Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to limit group_concat to 3?

Posted on 2010-08-12
10
Medium Priority
?
3,652 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

877 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