MySQL

Trouble using ORDER BY in a UNION statement

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Oct 2007

Posts: 260

Reputation: Venom Rush is an unknown quantity at this point

Solved Threads: 2

Venom Rush Venom Rush is offline

Offline

Posting Whiz in Training

Trouble using ORDER BY in a UNION statement

Oct 8th, 2009

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.

 Help with Code Tags 
 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?

This user has a spatula. We don't know why, but we are afraid.

•

Join Date: Sep 2008

Posts: 123

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Oct 8th, 2009

Try this

 Help with Code Tags 
 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.

•

Join Date: Oct 2007

Posts: 260

Reputation: Venom Rush is an unknown quantity at this point

Solved Threads: 2

Venom Rush Venom Rush is offline

Offline

Posting Whiz in Training

Oct 8th, 2009

•

Originally Posted by cgyrob

Try this
Help with Code Tags

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.

This user has a spatula. We don't know why, but we are afraid.