Q:

mysql union selects with 1 distinct condition

I have a huge select, basically 3 selects glued together with 2 UNIONs. All the select columns are the same within each of my selects. The one thing that is different in every one of them is the WHERE clausule. All of them have almost the same where conditions (there is only one condition in each of them that differs from others). My statements goes like this
(SELECT column_name1, column_name2, column_name3, ....
FROM users
LEFT JOIN ...
    ON ...
LEFT JOIN ...
(bunch of LEFT JOINS)
.
.
.
WHERE (SAME CONDITIONS) AND program_id = 3 order by RAND() limit 100)
UNION
(SELECT column_name1, column_name2, column_name3, ....
FROM users
LEFT JOIN ...
    ON ...
LEFT JOIN ...
(bunch of LEFT JOINS)
.
.
.
WHERE (SAME CONDITIONS) AND program_id = 2 order by RAND() limit 100)
(SELECT column_name1, column_name2, column_name3, ....
FROM users
LEFT JOIN ...
    ON ...
LEFT JOIN ...
(bunch of LEFT JOINS)
.
.
.
WHERE (SAME CONDITIONS) AND program_id = 1 order by RAND() limit 100)

And from this select I also want to select some other data. I do it like this because I want to have different order of users but the order of them needs to be always from highest program to the lowest. The question is Can I somehow make this select shorter? Because it doesn't make sense to have almost everything the same but 1 condition. And when I find a bug in there or want to change something (because this is only the part of the select) I have to rewrite everything.
Can you please help me ?

A:

You could try the following. It orders the rows first by program_id and then by random. It makes use of user-defined variables to then rank the rows, starting over from 1 every time a new program_id is reached. Finally it only returns rows where rank is below or equal to 100.
SELECT
  id,
  program_id

FROM(
  SELECT
    id,
    program_id,
    @rank := IF(@program = program_id, @rank + 1, 1) AS rank,
    @program := program_id

  FROM(
    SELECT
      id,
      program_id

    FROM users

    WHERE program_id IN(1, 2, 3)
      #AND [other conditions]

    ORDER BY
      program_id,
      RAND()
  ) AS Derived

  JOIN (SELECT @program := 0, @rank := 0) AS var
) AS Derived

WHERE rank <= 100

ORDER BY
  program_id,
  id DESC

http://sqlfiddle.com/#!2/92d7b/1

