0

I'm trying to write a query that returns a fixed number of results in a group concat. I don't think it's possible with a group concat, but I'm having trouble figuring out what sort of subquery to add.

Here's what I would like to do:

Query

select id,
group_concat(concat(user,'-',time) order by time limit 5)
from table
where id in(1,2,3,4)
group by 1

When I remove the "limit 5" from the group concat, the query works but spits out way too much information.

I'm open to structuring the query differently. Specific ID numbers will be supplied by the user of the query, and for each ID specified, I would like to list a fixed number of results. Let me know if there is a better way to achieve this.

Ullas
  • 11,450
  • 4
  • 33
  • 50
onomatopoeia
  • 7
  • 1
  • 6
  • 1
    If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Oct 15 '14 at 13:34

1 Answers1

0

Not sure the exact result set you want, but check out this SO post: How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?

As another example, I tried out the query/solution provided in the link and came up with this:

SELECT user_id, SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT date_of_entry),',',5) AS logged_dates FROM log GROUP BY user_id;

Which returns: user_id | logged_dates 1 | "2014-09-29,2014-10-18,2014-10-05,2014-10-12,2014-10-19" 2 | "2014-09-12,2014-09-03,2014-09-23,2014-09-22,2014-10-13" 3 | "2014-09-10" 6 | "2014-09-29,2014-09-27,2014-09-26,2014-09-25" 8 | "2014-09-26,2014-09-30,2014-09-27" 9 | "2014-09-28" 13 | "2014-09-29" 22 | "2014-10-12"

The above query will return every user id that has logged something, and up to 5 dates that the user has logged. If you want more or less results form the group concat, just change the number 5 in my query.

Following up, and merging my query with yours, I get:

SELECT user_id, SUBSTRING_INDEX(GROUP_CONCAT(date_of_entry ORDER BY date_of_entry ASC),',',3) AS logged_dates FROM log WHERE user_id IN(1,2,3,4) GROUP BY user_id

Which would return (notice that I changed the number of results returned from the group_concat): user_id | logged_dates 1 | "2014-09-16,2014-09-17,2014-09-18" 2 | "2014-09-02,2014-09-03,2014-09-04" 3 | "2014-09-10"

Community
  • 1
  • 1
persinac
  • 66
  • 1
  • 7