Search code examples
mysqlsqlgroup-concat

Using MYSQL GROUP_CONCAT with sub query

I am trying to get my head around using GROUP_CONCAT within MYSQL.

Basically I have the following table, table1:

id, field1, field2, active

I want to bring back 5 rows within the table but in random order. So I'm using this:

SELECT GROUP_CONCAT(id ORDER BY rand()) FROM table1 WHERE active=1

This behaves as I would expect. I then want to use the output to select the other columns (field1, field2) from the table and display the results. So I've tried using:

SELECT *
FROM table1
WHERE id IN
(
    SELECT GROUP_CONCAT(id ORDER BY rand()) as id FROM table1 WHERE active=1
);

I expected something like the above to work but I cant figure out why it doesn't. It DOES bring back results but not all of them, (i.e.) my table contains 10 rows. 6 rows are set to active=1. Therefore I would expect 6 rows to be returned ... this isn't happening I may get 1,2 or 0.

Additionally if it helps I'd like to limit the number of results returned by the sub-query to 3 but adding LIMIT doesn't seem to have any affect on the results returned.

Thank you in advance for your help

Solution

  • I think this is what you are looking for. This will bring back 5 random active rows.

    SELECT  *

FROM    table1

WHERE active=1

ORDER BY RAND()

LIMIT 5;