Testila Support Testila Support - 2 months ago 5
MySQL Question

Two queries union issue

I am currently working on a small PHP script on which I need to perform a union query.
I want to take 7 elements from a query and 1 element from another query both are performed on the same table .
The first one is this :

SELECT * FROM (
select * from quiz
where id != '10'
and langage = 'ar'
order by nshares
DESC LIMIT 15) a
order by rand() limit 7


The second one is:

select * from quiz
where id != '10'
and langage = 'ar'
order by qid DESC
limit 1


I tried

SELECT * FROM (
select * from quiz
where id != '10'
and langage = '$loga'
order by nshares DESC
LIMIT 15) a
order by rand()
limit 7
union
select * from quiz
where id != '10'
and langage = '$loga'
order by qid DESC
limit 1


But it doesn't seems to work.Can anyone help please ?

Answer

You are close. You just need parentheses around the subqueries:

(select q.*
 from (select q.*
       from quiz q
       where id <> '10' and langage = '$loga'
       order by nshares DESC
       limit 15
      ) q
 order by rand()
 limit 7
) union
(select q.*
 from quiz q
 where id <> '10' and langage = '$loga'
 order by qid desc
 limit 1
);

Here is a SQL Fiddle showing that the syntax works.

Notes:

  • union incurs overhead to remove duplicates. You should use union all if you don't want this overhead.
  • Your query could return 7 or 8 rows (assuming there is enough matching data), because the random row in the second query could match the first query.
  • Your syntax error is because order by is only allowed at the end of a union/union all query (unless you use subqueries).
  • If id is a number, don't use single quotes for the comparison. It confuses people and can confuse the optimizer.
Comments