Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Union Order By Question
08-22-2012, 05:34 AM #1
- Join Date
- Sep 2007
- Thanked 0 Times in 0 Posts
Union Order By Question
Im trying to order my query in two places, the first select, and the second select.
I dont wish to order my query as a whole.
SELECT * FROM jos_content WHERE introtext LIKE 'something' AND state = '1' UNION ALL SELECT * FROM jos_content WHERE state = '1'
Basicly i want something like this:
SELECT * FROM jos_content WHERE introtext LIKE 'something' AND state = '1' ORDER BY created DESC UNION ALL SELECT * FROM jos_content WHERE state = '1' ORDER BY created DESC
Could any one shed some light on this?
Any help will be much appreciated
08-22-2012, 02:42 PM #2
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 149 Times in 140 Posts
So really what you want is introtext that matches a certain word to come before the others and state=1 in either case?
SELECT foo, bar, qux FROM jos_content WHERE state=1 ORDER BY CASE WHEN introtext LIKE '%something%' THEN 0 ELSE 1 END, created DESC
By the way it may have only been your example but if you are looking for an exact match then you use = if you are using LIKE then you are looking for a word in introtext that either has text before or after it, thus the wild cards i've added.
The other thing i've done is removed quotes from around numeric data.