Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to limit group_concat to 3?

Posted on 2010-08-12
10
Medium Priority
?
3,550 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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

705 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