CODEWITHSUNDEEP
mysqlsqlpostgresqlwhere-clause
Alex
Alex

Reputation: 603

Ordering the results based on the where condition

Suppose that table1 has 3 attributes: first_name, last_name, and country. For example with the following tuples:

John White Canada
John Smith France
Mary Smith Canada
Ben  Smith Canada
Mary Black USA

I am looking for those with first name, "John", or last name "Smith", or country "US":

SELECT *
FROM table1
WHERE
    first_name='John' or
    last_name='smith' or
    country='US'

I want to get the result in the following order: First those with first name, John, then those with last name smith, and last those with country US

I know that I can write the following 3 different queries and then use their output in the order I want:

SELECT *
FROM table1
WHERE first_name='John'

SELECT *
FROM table1
WHERE last_name='smith'

SELECT *
FROM table1
WHERE country='US'

I am looking for a better way.

Question1: if I use the mentioned 3 queries and find their union the order will change, right? If yes, how can I append the results?

Question2: Is there a better way?

Upvotes: 2

Views: 56

Answers (2)

Tom H
Tom H

Reputation: 47444

You can use a CASE statement in your ORDER BY clause, like so:

SELECT    -- We NEVER use SELECT *
    first_name,
    last_name,
    country
FROM
    Table1
WHERE
    first_name = 'John' OR
    last_name = 'Smith' OR
    country = 'US'
ORDER BY
   CASE WHEN first_name = 'John' THEN 0 ELSE 1 END,
   CASE WHEN last_name = 'Smith' THEN 0 ELSE 1 END,
   CASE WHEN country = 'US' THEN 0 ELSE 1 END

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Your ORDER BY should be something like

ORDER BY CASE
           WHEN FIRST_NAME = 'John' THEN 1
           WHEN LAST_NAME = 'smith' THEN 2
           ELSE 3
         END

Upvotes: 5

Related Questions