Sorting

[Anon]

I somewhat new to sql and I am having problems trying to figure out how to preform a particular sort. I'll use a example that will keep it simple:

SELECT first_name, last_name
FROM Name
WHERE first_name LIKE '%tom%' OR last_name LIKE '%tom%'

Forgive me if there are errors in the query it's just to give the idea of what I'm doing. I would like to sort the results by column. So all occurences of '%tom%' in first_name and last_name would come first, all occurances of '%tom%' in only first_name would come second and finally all occurances of '%tom%' in only the last_name would complete the list.

Any ideas?

[tomhath]

I seems strange, but in SQL "CASE" behaves like a function, so you can use the value returned by a CASE for things like ordering. THis should be pretty close:

ORDER BY
case when (firstname like '%tom%' and lastname like '%tom%') then 0 else 1 end,
case when (firstname like '%tom%' and lastname not like '%tom%') then 0 else 1 end