Cannot execute GROUP_CONCAT

Question

I'm trying to return as GROUP_CONCAT 5 matches associated to the joined records, I tried to setup the query in the following way:

$sql = $this->db->prepare("SELECT l.*,
t.name as team_name,
r.name AS rank_name,
r.color AS rank_color,
GROUP_CONCAT(DISTINCT m.* LIMIT 5, ORDER BY m.id) AS last_five_matches,
FROM league_ranking l
LEFT JOIN team t ON l.team_id = t.id
LEFT JOIN competition_ranks r ON l.rank = r.id
LEFT JOIN `match` m ON m.home_team_id = l.team_id OR m.away_team_id = l.team_id
WHERE l.round_id = :round_id AND m.status = 5");

as you can see I want return 5 matches which have the status = 5.

The problem's that I get:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* LIMIT 5, ORDER BY m.id) AS last_five_matches, FROM league_ranking l

I don't understand where is the problem on the GROUP_CONCAT, notice that if I remove GROUP_CONCAT the query working, any help?

UPDATE

this is the query edited:

"SELECT l.*,
t.name as team_name,
r.name AS rank_name,
r.color AS rank_color,
GROUP_CONCAT(DISTINCT m.id ORDER BY m.id) AS last_five_matches
FROM league_ranking l
LEFT JOIN team t ON l.team_id = t.id
LEFT JOIN competition_ranks r ON l.rank = r.id
LEFT JOIN `match` m ON m.home_team_id = l.team_id OR m.away_team_id = l.team_id
WHERE l.round_id = :round_id AND m.status = 5
GROUP BY team_name"

and I get:

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'swp.l.position' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Maybe you should get your query straight first. You are outer joining `team` and `competition_ranks`, because you expect to find `league_ranking` without team or rank? That doesn't seem to make much sense. You are outer joining `match` only to dismiss all outer joined records in your where clause? (`m.status = 5` dismisses those records, because in outer joined records the columns are null.) You want one aggregated row (by using `GROUP_CONCAT` without `GROUP BY`)? But what then are `team_name`, `rank_name` and `rank_color` supposed to contain? Or do you want an aggregation per team and rank? — Thorsten Kettner, Jun 18 '18 at 08:40
As to `GROUP_CONCAT(... LIMIT ...)`: I don't think that's supported. It's not in the docs (https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat) at least, and were it supported, I'd expect the `LIMIT` to come after `ORDER BY`. — Thorsten Kettner, Jun 18 '18 at 08:43
@ThorstenKettner I discovered it's not supported, unfortunately I cannot get 5 records from `GROUP_CONCAT` — popop, Jun 18 '18 at 08:44
Okay. What do the tables represent? I'd expect each team to play in exactly one league and have one rank in it. This could be just two columns in the team table. But I see there is a league_ranking table. What does it represent? Can there be two entries in the table for the same team? Can a team have two different ranks? What is a rank exactly? I'd just expect rank #1, #2, etc., but you have a table with ranks and those ranks have names. Can you give examples? — Thorsten Kettner, Jun 18 '18 at 08:50
your updated error is now duplicate, https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql — Aurel, Jun 18 '18 at 08:52
And you want `GROUP_CONCAT` to show concatenated match IDs, say `'44,36,33,27,25'`? Are you sure? — Thorsten Kettner, Jun 18 '18 at 08:53
@ThorstenKettner the rank isn't the position, a rank represent the competition ranking achieved by the team position. Suppose that a team with name Foo, will end the league in the first position, this team will achieve as rank the champions league qualification. Each rank give access to other competition for the next season based on the position achieved on the end of current season. — popop, Jun 18 '18 at 08:53
@ThorstenKettner yes, I need to return just 5 matches, because a team can play at least 40+ matches in a league, I just need the 5 recent matches which have as status 5 that represent "finished" — popop, Jun 18 '18 at 08:54
A team may have changed league and played two games in the new league. So you want to show these two games and the last three played in the old league? Or do you want two rows, one for the new league with two matches, one for the old league with five matches? And again: you really only want to show match IDs? It might be best you show some sample data and the expected result in your request. — Thorsten Kettner, Jun 18 '18 at 09:09

Aurel · Answer 1 · 2018-06-18T08:31:31.607

0

According to the mysql documentation you can't use LIMIT statement with GROUP_CONCAT(). Try this one :

.. GROUP_CONCAT(DISTINCT m.id, ORDER BY m.id) AS last_five_matches ..

edited Jun 18 '18 at 08:31

answered Jun 18 '18 at 08:23

Aurel

3,682
1
19
22

when I remove `LIMIT` I get: `Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ORDER BY m.id) AS last_five_matches, FROM league_ranking l LEFT JOIN' at line 5` and also in this way I don't take 5 matches but all the matches which have as status 5... – popop Jun 18 '18 at 08:24
And replace `m.*` with a specific field like `m.id`. – Aurel Jun 18 '18 at 08:31
okay, but now I get: `SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5, ORDER BY m.id) AS last_five_matches, FROM league_ranking l LE' at line 5` – popop Jun 18 '18 at 08:36
try without `LIMIT` and with a specific field – Aurel Jun 18 '18 at 08:39

asela38 · Answer 2 · 2018-06-18T08:30:57.927

0

GROUP_CONTACT is an Aggregation Operation which you need to have a GROUP BY clause describe under which values you are aggregating the records

SELECT l.*,
   t.name as team_name,
   r.name AS rank_name,
   r.color AS rank_color,
   GROUP_CONCAT(DISTINCT m.<column_name> LIMIT 5, ORDER BY m.id) AS last_five_matches,
FROM league_ranking l
   LEFT JOIN team t ON l.team_id = t.id
   LEFT JOIN competition_ranks r ON l.rank = r.id
   LEFT JOIN `match` m ON m.home_team_id = l.team_id OR m.away_team_id = l.team_id
WHERE l.round_id = :round_id AND m.status = 5
GROUP BY team_name, rank_name, rank_color

edited Jun 18 '18 at 08:30

answered Jun 18 '18 at 08:24

asela38

4,546
6
25
31

with your query I get: `QLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* LIMIT 5, ORDER BY m.id) AS last_five_matches, FROM league_ranking l ' at line 5` – popop Jun 18 '18 at 08:25
I have edited the code you need to specify the column which you need to concat – asela38 Jun 18 '18 at 08:30
but if I need to get all the columns? I must write it manually? – popop Jun 18 '18 at 08:36
Then you should concat those columns GROUP_CONCAT(DISTINCT CONCAT(m.x, "-", m.y, "-", m.z), ORDER BY m.id) – asela38 Jun 18 '18 at 10:17

score 0 · Accepted Answer · answered Jun 18 '18 at 10:57

I believe you want:

SELECT t.name as team_name, r.name AS rank_name, r.color AS rank_color,
       SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT m.id ORDER BY m.id), ',', 5) AS last_five_matches
FROM team t LEFT JOIN
     league_ranking l
     ON l.team_id = t.id LEFT JOIN
     competition_ranks r
     ON l.rank = r.id LEFT JOIN
     `match` m
     ON m.home_team_id = l.team_id OR m.away_team_id = l.team_id
WHERE l.round_id = :round_id AND m.status = 5
GROUP BY t.name, r.name, r.color;

Notes:

The GROUP BY keys exactly match the unaggregated columns in the SELECT.
I removed the l.*. It doesn't make sense in the GROUP BY query.
GROUP_CONCAT() doesn't support LIMIT. Sounds like a good idea, but it doesn't exist. Instead, form the list then take the first 5 elements.
I change the JOIN order to start with team. You seem to want one row per team, so that should be the first table in the FROM.

Cannot execute GROUP_CONCAT

3 Answers3