Page 1 of 1

Problem: Sorting while using UNION

Posted: Mon Sep 03, 2007 7:39 am
by MarK (CZ)
hi again :)

I'm trying to do multiple queries in one like this (using UNION):

Code: Select all

(SELECT time_total
 FROM los_results
 WHERE competition_id = '3'
 ORDER BY time_total ASC)
UNION
(SELECT time_total
 FROM los_results
 WHERE competition_id = '7'
 ORDER BY time_total ASC)
UNION
...
The problem is, the queries don't get sorted at all. If I run the single query alone, it sorts well but not when using UNION.
(and no, I don't want to sort the whole result, just the single queries)
Is there some limitation with using UNION that I don't know about?

Thanks in advance ;)

Posted: Mon Sep 03, 2007 8:04 am
by VladSun
What about NOT using unions:

Code: Select all

SELECT time_total
FROM los_results
WHERE competition_id in (3,7,10)
ORDER BY competition_id, time_total ASC

Posted: Mon Sep 03, 2007 8:14 am
by MarK (CZ)
I won't get what I want with that :(

Anyway, I'm curious why it doesn't work with the unions like I thought...

Posted: Mon Sep 03, 2007 8:20 am
by VladSun
It works for me - results are sorted ...

What are you trying to get as a result?

Posted: Mon Sep 03, 2007 8:27 am
by MarK (CZ)
It doesn't get sorted on my machine :(
Any idea why? anyone?

I just wanted to get it sorted by competition_id and time_total. But I wanted competition_id to be sorted as it is in the db originnaly, that means I couldn't just put it in the ORDER BY clause. I've already solved it some other way.

But still I would like to know why the UNIONs didn't work if anyone has any idea :)