ORDERing by WHERE condition

anjanesh · #1 (**permalink**) 08-14-07, 11:44

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

r937 · #2 (**permalink**) 08-14-07, 11:51

Code:

SELECT some
     , specific
     , columns
  FROM `tbl` 
 WHERE Name LIKE '%John%'
ORDER
    BY CASE WHEN Name='John'
            THEN 42
            ELSE 937 END

anjanesh · #3 (**permalink**) 08-14-07, 12:19

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%'

r937 · #4 (**permalink**) 08-14-07, 12:39

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

anjanesh · #5 (**permalink**) 08-21-07, 10:44

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

r937 · #6 (**permalink**) 08-21-07, 11:06

it looks like you understand how CASE works

Internet Services

Web Development

Digital Marketing

Consumer Tech