Problem: Sorting while using UNION

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Problem: Sorting while using UNION

Post 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 ;)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post 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...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

It works for me - results are sorted ...

What are you trying to get as a result?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post 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 :)
Post Reply