Ad:

MySQL Discussion Thread
Marked Solved
Views: 978

Oct 8th, 2009

Trouble using ORDER BY in a UNION statement

Expand Post »

As the title suggests, I'm having trouble sorting the results of a union statement. I'm trying to sort the results by date descending and it's coming out ascending.

mysql Syntax (Toggle Plain Text)
(SELECT a, b, c, d, e, f, DATE FROM table1 WHERE a=10 AND b=1) UNION (SELECT a, b, c, d, e, f, DATE FROM table1 WHERE a=11 AND b=1) ORDER BY DATE DESC;
(SELECT a, b, c, d, e, f, date FROM table1 WHERE a=10 AND b=1) UNION (SELECT a, b, c, d, e, f, date FROM table1 WHERE a=11 AND b=1) ORDER BY date DESC;

I'm selecting all the same rows from the same table. The reason I'm using a UNION statement is because I have 3 different WHERE conditions.

This really should work and I've gone over my code with a fine tooth comb. It's driving me crazy. Anyone have any suggestions?

Similar Threads

ORDER BY in MySQL
"{" expected .. please help me in Java
Need help with mysql select statement and checkboxes in PHP
Sql + access - less than end date not working in MS Access and FileMaker Pro
SQL Union/ASP problem in MS SQL

Venom Rush

Reputation Points: 18

Solved Threads: 2

Posting Whiz

Offline

325 posts
since Oct 2007

Permalink

Oct 8th, 2009

Re: Trouble using ORDER BY in a UNION statement

Try this

sql Syntax (Toggle Plain Text)
SELECT a,b,c,d,e,f FROM 
(
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=10 AND b=1) 
  UNION 
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=11 AND b=1) 
)
  ORDER BY DATE DESC
Select a,b,c,d,e,f from 
(
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=10 AND b=1) 
  UNION 
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=11 AND b=1) 
)
  ORDER BY DATE DESC

You need to encapsulate the whole union to order them after all the records are retrieved.

Last edited by cgyrob; Oct 8th, 2009 at 12:19 pm.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Oct 8th, 2009

Re: Trouble using ORDER BY in a UNION statement

Click to Expand / Collapse Quote originally posted by cgyrob ...

Try this
sql Syntax (Toggle Plain Text)
SELECT a,b,c,d,e,f FROM 
(
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=10 AND b=1) 
  UNION 
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=11 AND b=1) 
)
  ORDER BY DATE DESC
Select a,b,c,d,e,f from ( (SELECT a, b, c, d, e, f, DATE FROM table1 WHERE a=10 AND b=1) UNION (SELECT a, b, c, d, e, f, DATE FROM table1 WHERE a=11 AND b=1) ) ORDER BY DATE DESC
You need to encapsulate the whole union to order them after all the records are retrieved.

Hi cgyrob

Thanks for the response. My original statement is actually correct. I was being blonde and didn't change a class variable on my display page which was causing the issues.

Venom Rush

Reputation Points: 18

Solved Threads: 2

Posting Whiz

Offline

325 posts
since Oct 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Help with generating a unique number

Next Thread in MySQL Forum Timeline: Help...! (Sub Query)

DaniWeb Message

Cancel Changes

User Name
Password