-2
Select group_concat(ID SEPARATOR '|') 
  from TableJ J
  Left Join TableL L on L.J_ID=J.ID
  Left Join TableB B on B.LJ_ID=L.F_ID
  Left Join TableLJ LJ on LJ.ID=L.F_ID
Group 
    by J.ID

The output returns records like

  • 103237|43775|84462|19153|54618|108646|50142|96946|37251|75984|54524
  • 29728|46758|65987|20772|34513|61323|2778|32630|53616|103450|27152|37278|65950|13837|33500|59490

etc

The issue is I need to limit the # of records/pipes in the results to 5 records.

In TableLJ there is a field Population which I can use to sort and limit but no amount of fiddling allows me to specify I want to Select the top 5 from that table PER group.

Update is that I can do the following to limit the group_concat (still does not solve getting them sorted first by LJ.Population)

substring_index(group_concat(ID SEPARATOR '|'),'|',5)
Strawberry
  • 33,750
  • 13
  • 40
  • 57
user3649739
  • 1,829
  • 2
  • 18
  • 28
  • Hi. Select top n per group is a faq. (Obviously.) (It has a tag: [limit-per-group].) Please research before you consider asking. See [ask]. – philipxy Oct 19 '18 at 23:34
  • @philipxyObviously WHAT? I did research and asked it the way I could figure out how to ask it. Are you saying I should have KNOWN it was called ilmit-per-group or there was such a thing? – user3649739 Oct 19 '18 at 23:42
  • Hi. Re "obviously", it is obvious that a query with a straightforward meaning like that will have been asked many times. Re research, just googling your *clear* *specific* 'Select the top 5 from that table PER group' (without even 'sql' or 'site:stackoverflow.com') immediately returns answers. Re the tag, what I said was that it exists. I am telling you helpful things. See [help] links. In any case we don't need to agree. ('top-n-per-group' would be a good synonym--but one isn't limited to searching names of tags. LIMIT & TOP are synonymous keywords in different SQLs.) – philipxy Oct 20 '18 at 00:13
  • [Here's my cut & paste for faqs. Clearly I think that askers (time & again every day) don't know & would benefit from at least some of it.] Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Oct 20 '18 at 00:22
  • @philipxy I always do search google and here for my answers, thus you see fairly few and in-between (and relevant) questions. I did not find any answer that made sense to my use case and in fact still have not. I'll continue to google however – user3649739 Oct 20 '18 at 00:52
  • 1
    What is your MySQL server version ? – Madhur Bhaiya Oct 20 '18 at 05:18
  • Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/q/2129693/3404097) – philipxy Oct 20 '18 at 06:20
  • The google search I mentioned gives me [a question with 162 votes](https://stackoverflow.com/q/176964/3404097) with answers that work in MySQL 8.0 including a 16 vote answer that works in all MySQLs. But googling with 'mysql' gives the 303 vote proposed duplicate. – philipxy Oct 20 '18 at 06:26
  • 1
    What (if anything) do you intend to do with the result? – Strawberry Oct 20 '18 at 07:24
  • 1
    Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) – Nick Oct 20 '18 at 08:14
  • @MadhurBhaiya 5.5.55-0+deb8u1 – user3649739 Oct 20 '18 at 12:37

1 Answers1

0
  • Group_Concat() function has an optional Order By clause, using which we can sort the column value(s) to concat, based on another/same column(s).
  • You can use Substring_Index() function to restrict the concatenated string to 5 per group.

Try:

SELECT Substring_Index(Group_concat(ID 
                                    ORDER BY LJ.Population ASC 
                                    SEPARATOR '|'), 
                       '|', 
                       5) 
FROM   TableJ AS J
       LEFT JOIN TableL AS L
              ON L.J_ID = J.ID
       LEFT JOIN TableB AS B
              ON B.LJ_ID = L.F_ID
       LEFT JOIN TableLJ AS LJ
              ON LJ.ID = L.F_ID
GROUP  BY J.ID

In MySQL 8.0.2 and onwards, we can utilize Window Functions, to determine Row_Number(), within a partition for J.ID. We can sort by LJ.Population to get the row number accordingly.

Now, use this result-set as a Derived Table and consider only the rows upto row number 5. You can then do Group By and Group_Concat() accordingly.

SELECT   Group_Concat(dt.ID ORDER BY dt.rownumber ASC SEPARATOR '|')
FROM     (
          SELECT    J.ID,
                    ROW_NUMBER() OVER (PARTITION BY J.ID
                                       ORDER BY LJ.Population ASC) AS rownumber
          FROM      TableJ   AS J
          LEFT JOIN TableL   AS L
            ON  L.J_ID = J.ID
          LEFT JOIN TableB   AS B
            ON  B.LJ_ID = L.F_ID
          LEFT JOIN TableLJ  AS LJ
            ON  LJ.ID = L.F_ID ) AS dt
WHERE    dt.rownumber <= 5
GROUP BY dt.ID
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57