Solved

union & order by

Posted on 2013-11-17
1
405 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

815 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

7 Experts available now in Live!

Get 1:1 Help Now