How would I order a table by the number of matching params in the where clause of an sql statement?

Question

I'm writing sql to search a database by a number of parameters. How would I go about ordering the result set by the items that match the most parameters in the where clause. For example:

SELECT * 
FROM users 
WHERE username = 'eitan' 
OR email = '[email protected]' 
OR company = 'eitan'


Username   |  email                |   company

1) eitan   |     [email protected]     |     blah

2) eitan   |     [email protected]   |    eitan

3) eitan   |    [email protected]    |     blah

should be ordered like:

2, 3, 1.

Thanks. (ps the query isn't that easy, has a lot of joins and a lot of OR's in the WHERE)

Eitan

Answer 1

If MySQL:

SELECT * FROM users 
ORDER BY 
    (username = 'eitan') 
    + (email = '[email protected]') 
    + (company = 'eitan') 
    DESC

If PostgreSQL:

SELECT * FROM users 
ORDER BY 
    (username = 'eitan')::int 
    + (email = '[email protected]')::int 
    + (company = 'eitan')::int 
    DESC

If Sql Server:

SELECT * FROM users 
ORDER BY 
    case when username = 'eitan' then 1 else 0 end
    + case when email = '[email protected]' then 1 else 0 end
    + case when company = 'eitan' then 1 else 0 end
    DESC