Different ORDER BY based on criteria matched?

Question

Is it possible with SQL to order the returned rows differently based on what criteria was matched?

For example, my webpage allows inputting a name or zip code. The query currently orders by last name, first name. That's great, unless they input a zip code I'd then want to order the results by zip code instead of name. Is that possible? I'd like this to occur in the same query rather than two. A potential problem: What if the inputted value matches both the name and zip?

Thanks!

Pseudo query:

SELECT firstName, lastName, zip
From Users
WHERE (FirstName LIKE 'search' OR LastName LIKE 'search' OR Zip LIKE 'search')
ORDER BY LastName, FirstName

Answer 1

Since the query is formulated in a way to guess which field matches the search condition, you may want to try guessing which one has matched in the ORDER BY as well:

SELECT firstName, lastName, zip
From Users
WHERE (FirstName LIKE 'search' OR LastName LIKE 'search' OR Zip LIKE 'search')
ORDER BY 
    CASE WHEN FirstName LIKE 'search' THEN FirstName ELSE NULL END
,   CASE WHEN LastName LIKE 'search' THEN LastName ELSE NULL END
,   CASE WHEN Zip LIKE 'search' THEN Zip ELSE NULL END

This assumes, of course, that 'search' stands for something that you pass to your query that is a meaningful parameter of the LIKE operator, i.e. has metacharacters, has been properly formatted, and so on.