How do i collapse these 3 queries into one?

Question

Hi i am trying to select 10x3 random id's from 3 different categories, but is there a smarter way to achieve this in just one query instead of 3 different queries?

$sql = "SELECT id FROM rating WHERE category IN (1) ORDER BY RAND() LIMIT 10";
$result1 = $dbCon->query($sql);

$arr;
while ($obj = $result1->fetch_object()) {
$arr[] = $obj->id;
}

$sql = "SELECT id FROM rating WHERE category IN (2) ORDER BY RAND() LIMIT 10";
$result1 = $dbCon->query($sql);

while ($obj = $result1->fetch_object()) {
$arr[] = $obj->id;
}

$sql = "SELECT id FROM rating WHERE category IN (3) ORDER BY RAND() LIMIT 10";
$result1 = $dbCon->query($sql);

while ($obj = $result1->fetch_object()) {
$arr[] = $obj->id;
}


var_dump($arr);

Possible duplicate of http://stackoverflow.com/questions/4230780/mysql-query-with-multiple-limits — manian, Apr 17 '17 at 11:54

score 5 · Accepted Answer · answered Apr 17 '17 at 11:50

5

You can use UNION ALL, e.g.:

SELECT id FROM rating WHERE category IN (1) ORDER BY RAND() LIMIT 10

UNION ALL

SELECT id FROM rating WHERE category IN (2) ORDER BY RAND() LIMIT 10

UNION ALL

SELECT id FROM rating WHERE category IN (3) ORDER BY RAND() LIMIT 10

answered Apr 17 '17 at 11:50

Darshan Mehta

30,102
11
68
102

I am not sure how i use this in my example? – ii iml0sto1 Apr 17 '17 at 12:30
`$sql = ""; $result1 = $dbCon->query($sql);` – Darshan Mehta Apr 17 '17 at 12:38

Gordon Linoff · Answer 2 · 2017-04-17T12:09:07.850

0

This is not easy. Your method might be the best approach. I mean, union all between the queries is marginally better, but given the overhead of doing the ORDER BY rand(), it is not going to be much improvement.

One alternative method in MySQL is to use variables:

SELECT id
FROM (SELECT r.*,
             (@rn := if(@c = category, @rn + 1,
                        if(@c := category, 1, 1)
                       )
             ) as rn
      FROM rating r CROSS JOIN
           (SELECT @c := -1, @rn := 0) params
      WHERE category IN (1, 2, 3)
      ORDER BY category, RAND()
     ) r
WHERE rn <= 10;

The advantage of this approach is that you don't have to specify the categories that you want explicitly. You can add new categories just by changing the IN list, or remove the WHERE clause entirely and get up to 10 random rows for all categories.

edited Apr 17 '17 at 12:09

answered Apr 17 '17 at 11:48

Gordon Linoff

1,242,037
58
646
786

yes i got surprised that you have the reputation i can not imagine but you got a downvote :O – lazyCoder Apr 17 '17 at 12:08
@BunkerBoy . . . My reputation should have almost nothing to do with it. This is a correct answer and might be better than `union all` under some circumstances. (The `union all` might be better under other circumstances.) – Gordon Linoff Apr 17 '17 at 12:10
1

i know man that answer is not about the answerer repo but the user should give a proper explanation after down vote to any one – lazyCoder Apr 17 '17 at 12:12
Waow this looks pretty advanced, i will have to study this quite a bit i havent used sql like this before, i assume the @ is how to declare variables? – ii iml0sto1 Apr 17 '17 at 12:28
@GordonLinoff just to clarify, I did not downvote it (why would I?). – Darshan Mehta Apr 17 '17 at 12:38

How do i collapse these 3 queries into one?

2 Answers2