Solved

union & order by

Posted on 2013-11-17
1
404 Views
Last Modified: 2013-11-18
Hi there,
There are 2 SELECT statements joined by UNION.
The ORDER BY comes at the end.
The question is:  Can I  also put an ORDER BY after every SELECT? If I can, does that help the performance?

eg:

SELECT * FROM (SELECT  * FROM TABLE1  ) ORDER BY ID DESC LIMIT 500 ) AS T1
UNION ALL
SELECT * FROM (SELECT  * FROM TABLE2  ) ORDER BY ID  DESC LIMIT 500 ) AS T2
ORDER BY DATETIME DESC
0
Comment
Question by:myyis
1 Comment
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 39655363
MySQL 5.5 Reference Manual :: 13.2.9.4. UNION Syntax

Most of that article pertains to the use of ORDER BY with UNION.

As a sidenote, if you want to improve performance, remove the double-SELECT from your statements:
(SELECT * FROM TABLE1 ORDER BY ID DESC LIMIT 500)
UNION ALL
(SELECT * FROM TABLE2 ORDER BY ID DESC LIMIT 500)
ORDER BY DATETIME DESC

Open in new window

To further improve performance, don't "SELECT *" - identify the columns you need and include only those columns in your field list.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now