UNION is ruining my ORDER BY!

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
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

UNION is ruining my ORDER BY!

Post by visionmaster »

Hello together!

Please read my posting, even of seems to be long at first sight..... I really would be thankful for your help!

MySQL 4.1.11
------------

Code: Select all

(
SELECT t1.firmenname, t1.strasse, t2.platzierungsfolge AS ranking
FROM firmendaten AS t1
JOIN platzierungen AS t2 ON t2.id_firmendaten = t1.idfirmendaten
WHERE t2.platzierungswort = 'maschine'
ORDER BY ranking ASC
)

Result:
-------
firmenname strasse ranking
Stadlberger Maschinenbau GmbH Lengloher Str. 1
Roessner Maschinenbau GmbH Alte Liederbacher Str. 2
Kaiser Ventilatorenbau KG Herrntrop 3
Expectra Deutschland GmbH Heidenkampsweg 4
Babylon Stahlbau GmbH Zeppelinstr. 5


=> O.k. fine, I get exactly the results I want.


=> Now the same queries but this time with the use of UNION:

1. Search in the table 'platzierungen' and sort the results ascending by the field 'platzierungsfolge. So if somebody enters 'machine', rows with 'machine' held in the table 'platzierungen' should be shown first, ordered by 'platzierungen'. A kind of ranking for customers who payed money to be shown on position 1,2,3,... for the search word 'machine'.

2. After the "payed" entries are shown , the search word should be found in the table 'firmendaten' (company data). There the field 'produkte' should be searched, with LIKE %machine%. Here another sorting, this time ORDER BY 'logo_ranking'.

O.k., here is my UNION statement:


MySQL 4.1.11
------------

Code: Select all

(
SELECT t1.firmenname, t1.strasse, t2.platzierungsfolge AS ranking
FROM firmendaten AS t1
JOIN platzierungen AS t2 ON t2.id_firmendaten = t1.idfirmendaten
WHERE t2.platzierungswort = 'maschinenbau'
ORDER BY ranking
)
UNION (

SELECT firmenname, strasse, logo_ranking
FROM firmendaten
WHERE produkte LIKE '%maschinenbau%'
ORDER BY logo_ranking
)

Result:
-------

firmenname strasse ranking
Roessner Maschinenbau GmbH Alte Liederbacher Str. 2
Kaiser Ventilatorenbau KG Herrntrop 3
Hendrik Hilbring Maschinenbau Besslinghook 12
Expectra Deutschland GmbH Heidenkampsweg 4
Krifft & Zipsner GmbH Industriestraße 7
Everhard Löcher GmbH Lenenweg 8
Thomas Metz Maschinenbau GmbH Nordstrasse 10
Babylon Stahlbau GmbH Zeppelinstr. 5
Stadlberger Maschinenbau GmbH Lengloher Str. 1
Bräuer Maschinen- und Freiberger Straße 6
ORGA GmbH Wachhausstr. 11
Christian Weiße Gabelsbergerstraße 0
Jentec GmbH Meißner Straße 1
JACOBI Systemtechnik GmbH An der Prießnitzaue 0
FG Fußbodengeräte- und Am Karrenberg 1
Portatec Maschinenbau GmbH Mittelstraße 0
IKB Ingenieur- Tzschianer Straße 0
Forschungsvereinigung Heidelberger Straße 0

=> That is absolutely WRONG! (2,3,12,4,7,8,...) How can this be? Why aren't the first results sorted by 'ranking'? Just a complete mess?! Why does the 2nd statement mess up the order of my first statement? Or what is going wrong?

=> Further question: Is my way of solving my problem totally wrong? I don't think so, but one never knows... ;-)

Thanks a lot!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: UNION is ruining my ORDER BY!

Post by timvw »

I think the ORDER clause is only allowed after the UNION. So you could try something like this (untested):

Code: Select all

(
SELECT t1.firmenname, t1.strasse, t2.platzierungsfolge AS ranking
FROM firmendaten AS t1
JOIN platzierungen AS t2 ON t2.id_firmendaten = t1.idfirmendaten
WHERE t2.platzierungswort = 'maschinenbau'
)

UNION 
(
SELECT firmenname, strasse, logo_ranking
FROM firmendaten
WHERE produkte LIKE '%maschinenbau%'
)

ORDER BY 3
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Re: UNION is ruining my ORDER BY!

Post by visionmaster »

Thanks a lot for your reply, but it unfortunately doesn't work...
Your suggested statement works, but doesn't return back the desired results. Now the results of the second query are shown first and the results of the first query are shown last, also in wrong order.

It seems ORDER BY 3 is ordering by 'logo_ranking' of the second statement.

Would it be better to separate the 2 queries and concatenate the result arrays together?

Or any other MySQL solution for my problem?

As I mentioned following should happen:

1. Search in the table 'platzierungen' and sort the results ascending by the field 'platzierungsfolge. So if somebody enters 'machine', rows with 'machine' held in the table 'platzierungen' should be shown first, ordered by 'platzierungen'. A kind of ranking for customers who payed money to be shown on position 1,2,3,... for the search word 'machine'.

2. After the "payed" entries are shown , the search word should be found in the table 'firmendaten' (company data). There the field 'produkte' should be searched, with LIKE %machine%. Here another sorting, this time ORDER BY 'logo_ranking'.

Really would appreciate your help!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: UNION is ruining my ORDER BY!

Post by timvw »

visionmaster wrote:Thanks a lot for your reply, but it unfortunately doesn't work...
Your suggested statement works, but doesn't return back the desired results. Now the results of the second query are shown first and the results of the first query are shown last, also in wrong order.

It seems ORDER BY 3 is ordering by 'logo_ranking' of the second statement.
order by 3 means that result should be sorted on the value of the 3th column. (couting starts from 1).

meaby it's a MySQL weirdness. You could try to change the alias from ranking to logo_ranking. But i would be surprised if that works.

visionmaster wrote: As I mentioned following should happen:

1. Search in the table 'platzierungen' and sort the results ascending by the field 'platzierungsfolge. So if somebody enters 'machine', rows with 'machine' held in the table 'platzierungen' should be shown first, ordered by 'platzierungen'. A kind of ranking for customers who payed money to be shown on position 1,2,3,... for the search word 'machine'.

2. After the "payed" entries are shown , the search word should be found in the table 'firmendaten' (company data). There the field 'produkte' should be searched, with LIKE %machine%. Here another sorting, this time ORDER BY 'logo_ranking'.
To me it appears both resultsets are best retrieved with 2 separate queries. I think it's even possible that MySQL will perform both simple queries faster than one complex query.
Post Reply