Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: ORDERing by WHERE condition

    Hi

    Code:
    SELECT * FROM `tbl` WHERE `Name` = 'John' OR `Name` LIKE '%John%'
    Lets say the result has 10 rows, 4 from `Name` = 'John' and 6 from `Name` LIKE '%John%'.

    I want the order such that the first 4 rows are from `Name` = 'John' and the last 6 rows are from `Name` LIKE '%John%'.

    Is that possible ?

    Thanks
    MySQL 5.1

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT some
         , specific
         , columns
      FROM `tbl` 
     WHERE Name LIKE '%John%'
    ORDER
        BY CASE WHEN Name='John'
                THEN 42
                ELSE 937 END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    1. Couldnt get the THEN 42 ELSE 937 part. Are you referring to a STORED PROCEDURE ?

    2. Is it possible if the query was
    Code:
    SELECT * FROM `tbl` WHERE `Name` = 'John' OR `Name` LIKE '%Smith%'
    MySQL 5.1

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. no, i'm not

    2. yes, it is

    please, why don't you do me a very small favour, and actually try my query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    This is excellent r937 - I did this and worked like a charm !
    Code:
    SELECT * FROM `tbl` WHERE
    `Name` LIKE '%an%'
    ORDER BY
    CASE
    WHEN `Name` LIKE 'an%' THEN 1
    WHEN `Name` LIKE '%an' THEN 1000
    ELSE 500
    END
    I think I got how this is sorting the result-set based on the CASE value matching against the WHERE clause, but is there some detailed explanation to this ?

    Docs doesnt have a sorting-by-where-clause example.

    Thanks
    MySQL 5.1

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it looks like you understand how CASE works

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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