Mysql – select random rows with max 3 rows per parent id

MySQLrandomsubquery

I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it returns to me Ascending id's each time I try.

SELECT id, rest_id, rn
FROM (SELECT (@rn := if(@rest_id = rest_id, @rn + 1,
                    if(@rest_id := rest_id, 1, 1)
                   )
         ) as rn,
         meals.*   
  FROM meals CROSS JOIN
       (SELECT @rn := 0, @rest_id := '') params
  ORDER BY rand() 
 ) meals
WHERE rn <= 3
order by id ASC

Best Answer

If you want random rows per parent_id, your inner ORDER BY should arrange rows in a way that same parent_id rows are grouped together. So, instead of

ORDER BY rand()

it should be

ORDER BY parent_id ASC, rand()

As for this:

it returns to me Ascending id's each time I try

The reason your query returns ascending IDs each time is because you have instructed it to return the results in the ascending order of id with this line at the end of the query:

ORDER BY id ASC

So, again, if you want the output to show same parent_id rows grouped together, add the parent_id column as the first sorting criterion to the final ORDER BY as well:

ORDER BY parent_id ASC, id ASC

Related Solutions

Mysql – How to make MySQL auto increment in random order

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant:

create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc
create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly):


SELECT
  MIN(ID) AS MIN_ID, Col1, Col2 
FROM 
  table 
GROUP BY 
 Col1, Col2

add a second INT field to be the first_index
update the table and set first_index to be the difference between the minimum value and the running value of the autoincremented field:


UPDATE 
  table t, temp_table tmp
SET 
  t.first_index = (t.ID - tmp.MIN_ID) + 1
WHERE 
  t.Col1=tmp.Col1 AND t.Col2=tmp.Col2

optionally remove the random and ID field, and add a new PK with the three fields Col1/Col2/first_index (auto incremented) for following inserts

Mysql – Select row with MAX(), or choose one if no MAX() exists

It is possible with UNION ALL. Basically, you have three criteria, therefore you need three SELECT statements. To simplify the select statements, lets create views from your selects as follows:

CREATE VIEW data1 AS
SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp <= t0
AND data.event <> 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event <> 'stopped';

CREATE VIEW data2 AS
SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp < t0
AND data.event = 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event = 'stopped';

Now, the select statement will be as easy as this one:

select data2.* 
from data1 inner join data2 
on data1.name_id = data2.name_id and data1.time_stamp = data2.time_stamp
UNION ALL
select data1.* 
from data1 inner join data2 
on data1.name_id = data2.name_id and data1.time_stamp > data2.time_stamp
UNION ALL
select data2.* 
from data1 inner join data2 
on data1.name_id = data2.name_id and data1.time_stamp < data2.time_stamp

SQL Fiddle (I used INT datatype for time_stamp column in the SQL Fiddle, but it shouldn't matter, as the principle is the same for timestamp datatype)

You do not need to use VIEWs, you can embed you queries into the above statement as subqueries. I have used the views just to demonstrate the technique

Best Answer

Related Solutions

Mysql – How to make MySQL auto increment in random order

Mysql – Select row with MAX(), or choose one if no MAX() exists

Related Question