0

I'm looking to fetch the last 10 item IDs in a single value separated by commas, like:

123,456,789 etc

However, MySQL just doesn't seem to work the way I think. I try:

SELECT GROUP_CONCAT(item.id) AS Item_IDs
FROM items
GROUP BY item.id
ORDER BY item.id DESC
LIMIT 10;

It just returns a column of rows where Item ID is 1 per row...

Item_IDs
1
2
3

It only works if I add another column, like item.status:

SELECT item.status, GROUP_CONCAT(item.id) AS Item_IDs
Item_Status Item_IDs
1 New
2 Processing
3 Completed

That's fine and all if I want to select something else along with ID... but I just want a list of X number of IDs in a comma-separated list in 1 column in 1 row.

I've done Google searches already which mostly just bring me to StackOverflow, and I'm not seeing anyone looking to do exactly what I am (or their issue isn't clueing me in on what I should be doing). MySQL docs are either not clarifying or just making things more muddy in my understanding with GROUP_CONCAT.

Hopefully this situation isn't incredibly unusual or the reality isn't just "MySQL or SQL doesn't work that way :/" or the solution isn't ridiculously complicated, but I'll take a ridiculously complicated solution over nothing.

Oh, I'm using MySQL 5.6 at the moment, but it would be nice to know the solution for 8 as well.

Asgar
  • 1,920
  • 2
  • 8
  • 17
SteveExdia
  • 321
  • 1
  • 3
  • 11
  • 1
    Having your tries is good and helpful, though could you explicitly state what your sample input table and expected output table are? – lemon Oct 14 '22 at 16:46
  • 1
    Why do you want to return the ids in a comma separated list? What are you doing with them afterwards? Usually it is not a good idea to put ids in a comma separated list. – Progman Oct 14 '22 at 16:54
  • `It just returns a column of rows where Item ID is 1 per row..` -> Because you do `GROUP BY item.id`, you will get one row per item.id in your result. – Luuk Oct 14 '22 at 17:30
  • `It only works if I add another column, like item.status` -> No this simply works because there is no `GROUP BY` on the field `item.id`. – Luuk Oct 14 '22 at 17:31
  • @lemon Why is a sample input table and expected output table required? You can already visualize it from the info I stated, and I already specified expected output: 123,456,789. I'm not seeing the need to draw a picture, especially considering someone provided a functional answer without needing a picture drawn for them. I feel like my question is already overly-explicit and far more visual than it needs to be, but I was just doing that because I get so many comments asking to be explicit with examples (generally that shouldn't be necessary, but maybe people aren't taught to think abstractly). – SteveExdia Dec 01 '22 at 19:46

2 Answers2

2

I'm looking to fetch the last 10 item IDs in a single value separated by commas

SELECT GROUP_CONCAT(id ORDER BY id DESC) AS Item_IDs
FROM ( SELECT id
       FROM items
       ORDER BY id DESC LIMIT 10
       ) AS subquery;
Akina
  • 39,301
  • 5
  • 14
  • 25
1

You could generate a long GROUP_CONCAT string, then use SUBSTRING_INDEX() to keep only the first 10 items:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC), ',', 10) AS Item_IDs
FROM items;

This should work on both MySQL 5.x and 8.0.

Admittedly, it may be somewhat costly because it will scan the whole table to create the string before applying the substring function. But if it's more important to you to avoid the subquery shown in other solutions, this could be an alternative.

It has been a feature request for MySQL to support a LIMIT clause inside the GROUP_CONCAT() function for a long time: https://bugs.mysql.com/bug.php?id=30098

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • That works but I get an error: "Row 147 was cut by GROUP_CONCAT(" I'm not quite sure what that means, but the solution seems incomplete. This is on MySQL 5.6. – SteveExdia Dec 01 '22 at 19:47
  • @SteveExdia https://stackoverflow.com/questions/45100917/mysql-error-row-xxxx-was-cut-by-group-concat – Bill Karwin Dec 01 '22 at 19:52