How to fetch table record IDs in comma-separated format?-CodePudding

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 | 123
2 | 456
3 | 789

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 | 123,456
2 | Processing | 789,890
3 | Completed | 234,567,876

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.

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

OK, I tried some varying Google searches, and one of them - mysql GROUP_CONCAT order limit - found an answer here that works:

https://stackoverflow.com/a/44926956/14744970

It required a sub-select (the other answers weren't good enough):

SELECT
    GROUP_CONCAT(id) AS Item_IDs
FROM (
    SELECT id FROM items ORDER BY id DESC LIMIT 10
) AS IDs
;

The list came out like this:

889,887,885,883,881,879,877,813,811,809

Yay!

Annoying but it worked.