Solved

How to limit group_concat to 3?

Posted on 2010-08-12
10
2,950 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now