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?
The CASE expression puts all the rows where you match the word you are looking for in introtext before the non matching rows and then orders them by created descending in both cases.Code: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.