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?

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._________________________
- Vijay G
Strive for Perfection

thank you :)

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:VW_Suppliers:--

Joe

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,

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

Cheers!

_________________________
- Vijay G
Strive for Perfection

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

thaks to you too :)

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.

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

Imar

__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004

Did this post help you? Click the button to show your appreciation!