-1

I got this sql query:

SELECT * 
FROM groups
LEFT OUTER JOIN group_questions
ON groups.id = group_questions.group_id 
WHERE group_questions.group_id = 1 ORDER BY group_questions.id DESC
UNION
SELECT * 
FROM groups
RIGHT OUTER JOIN group_questions 
ON groups.id = group_questions.group_id 
WHERE group_questions.group_id = 1 ORDER BY group_questions.id DESC

I'm trying to order it by doing this ORDER BY group_questions.id DESC like in the example above. But when I add it to the query it doesn't work at all. I've tried to search for an example but was unable to find one. Anyone here got an idea?

  • Your ORDER BY are fake - they will be ignored/lost during UNION. PS. `group_questions.id` is visible in each separate subquery but is NOT visible after UNION. – Akina Apr 14 '21 at 10:25
  • Does this answer your question? [Using union and order by clause in mysql](https://stackoverflow.com/questions/3531251/using-union-and-order-by-clause-in-mysql) – Alon Eitan Apr 14 '21 at 10:27

1 Answers1

0
(
SELECT * 
FROM groups
LEFT OUTER JOIN group_questions
ON groups.id = group_questions.group_id 
WHERE group_questions.group_id = 1
)
UNION
(
SELECT * 
FROM groups
RIGHT OUTER JOIN group_questions 
ON groups.id = group_questions.group_id 
WHERE group_questions.group_id = 1 
)
ORDER BY id DESC

You must sort combined rowset after UNION. And you must refer to the column name of combined rowset (which now does not refer to any source table of any separate subquery).

In this particular case the parenthesis wrapped separate subqueries are formally excess and may be removed. But I recommend to use them always - this removes possible ambiguity.

Akina
  • 39,301
  • 5
  • 14
  • 25
  • I tried your example and it comes back with an error #1250 - Tabel 'group_questions' from aSELECT can't be used in a global ORDER clause –  Apr 14 '21 at 10:43
  • @FilipGunnarsson *I tried your example* No. You does not use **my** code, you use the code which **you** imagine looking on my code without accuracy. – Akina Apr 14 '21 at 10:47
  • I dont understand what you mean –  Apr 14 '21 at 10:47
  • 1
    @FilipGunnarsson One more option - there are columns with the same names in both source tables. In this case you **MUST** delete the asterisk and list separate columns to be returned with unique aliases assigned. As a "good practice" - an asterisk instead of columns names list can be used in `COUNT(*)` expression only. – Akina Apr 14 '21 at 10:48
  • ah thats was true I had a unique column in both tables called id, i changed one to ai and now it works, thanks Akina –  Apr 14 '21 at 10:52