UNION and ORDER BY

darcome · September 4th, 2004, 06:34 AM

SELECT <some_columns>
FROM <some_tables>
ORDER BY <some_columns>
LIMIT 25,25
UNION
SELECT <some_columns>
FROM <some_tables>

but MySql says i'm using worng UNION and ORDER BY

What can i do?

happygv · September 4th, 2004, 01:13 PM

ORDER BY cannot be used with the inner select statements that are used for UNION.

You got to use that all the last, because, each select's result is combined together using union operator and ORDER BY can only be applied to the final result set, not for intermediate results. So shift your ORDER BY statement to the last as shown below.

Code:

SELECT <some_columns>
FROM <some_tables>
LIMIT 25,25
UNION 
SELECT <some_columns>
FROM <some_tables>
ORDER BY <some_columns>

_________________________
- Vijay G
Strive for Perfection

darcome · September 5th, 2004, 09:46 AM

thank you :)

joefawcett · September 5th, 2004, 11:23 AM

I can't swear by this as it's DB dependent but if you really need to sort individual sections you can use views that are combined -
VW_Customers:

Code:

SELECT TOP 100% Surname, Forenames FROM tblCustomer ORDER BY Surname

VW_Suppliers:

Code:

SELECT TOP 100% Surname, Forenames FROM tblSupplier ORDER BY Surname

Code:

Select * FROM VW_Customers

UNION ALL

Select * FROM VW_Suppliers

--

Joe

Imar · September 5th, 2004, 12:03 PM

Would it be possible to invent an ORDER BY column to order on?

Something like this:

SELECT <some_columns>, '1' AS OrderBY
FROM <some_tables>
UNION

SELECT <some_columns>, '2' AS OrderBY
FROM <some_tables>
ORDER BY OrderBy, <some_columns>

This way, when sorting by, say, surname, the matched records from the first SELECT statement will be listed first, sorted by surname, followed by the ordered records from the second select statement.

Or is there something really wrong with this solution?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

happygv · September 5th, 2004, 12:11 PM

Imar,

I don't see anything wrong with that. That works perfect. BTW, that was good thinking too.

Cheers!

_________________________
- Vijay G
Strive for Perfection

flibbergibbit · September 17th, 2004, 09:23 AM

darcome,
if you are using MySQL (not sure if this would work for other DBs) you can use ORDER BY for each SELECT, you just need to put parentheses around each SELECT statement

http://dev.mysql.com/doc/mysql/en/UNION.html

e.g.

(SELECT <some_columns>
FROM <some_tables>
ORDER BY <some_columns>
LIMIT 25,25)
UNION
(SELECT <some_other_columns>
FROM <some_other_tables>)
ORDER BY <some_other_columns>)

A couple of things to bear in mind are that:
1. Perhaps obviously, each SELECT statement that is being used must contain the same number of columns;
2. The data type of the columns that are used in the first SELECT statement will determine how the data retrieved from subsequent SELECT statements within the UNION are handled.

For instance, if the first column in the first SELECT statement is of type CHAR(5), then even if the first column in the second SELECT statement is of type TEXT, you will only get the first 5 characters of this column regardless of how much data is in it.

A workaround, although I'm sure someone knows a better way is to start the UNION off with a SELECT statement where each column is a type TEXT but doesn't SELECT anything e.g.

(SELECT <some_columns which are TEXT>
FROM <some_tables>
WHERE 1=0)
UNION
(SELECT <some_columns>
FROM <some_tables>
ORDER BY <some_columns>
LIMIT 25,25)
etc etc........

Apparently, this problem has been fixed in version 4.1.1 of MySQL.

cheers

darcome · September 19th, 2004, 11:15 AM

thaks to you too :)

grogi · June 24th, 2009, 07:22 AM

Yes, this query works fine but if you only have distinct rows. If you have union from same table but with different filters then you'll have a duplicates in the result set because of the column OrderBY.

Quote:

Originally Posted by Imar

Would it be possible to invent an ORDER BY column to order on?

Something like this:

SELECT <some_columns>, '1' AS OrderBY
FROM <some_tables>
UNION

SELECT <some_columns>, '2' AS OrderBY
FROM <some_tables>
ORDER BY OrderBy, <some_columns>

This way, when sorting by, say, surname, the matched records from the first SELECT statement will be listed first, sorted by surname, followed by the ordered records from the second select statement.

Or is there something really wrong with this solution?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar · June 24th, 2009, 03:46 PM

FYI: the thread is almost 5 years old.... ;-)

Imar