Union Order By Question

[bubaphex]

Hi All,

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.

My query:

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

[guelphdad]

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

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.

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.