Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Sep 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Order By Question

    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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    So really what you want is introtext that matches a certain word to come before the others and state=1 in either case?

    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
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •