Question 113180 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search