sql query with priorities on attributes

Question

hi there imagine I have a table like this

Id | Name       | City
1  | Tom york   | xx
2  | Jim york   | xy
3  | tony       | new York
4  | Sam york   | xz

and I would like to search records with name like '%york%' or city like '%york%' BUT i want to give more priority to name, so my rresult would be something like:

Id | Name       | City
1  | Tom york   | xx
2  | Jim york   | xy
4  | Sam york   | xz
3  | tony       | new York

that is, first the records where name like '%york%' and then the records where city like '%york%'

whats the best way to build this query? can I do it in a single query? answers in sql or linq to sql would be great

tks

Answer 1

select *
from (
    select Id, Name, City, 1 as Rank
    from MyTable
    where Name like '%a%' 
    union all
    select Id, Name, City, 2 as Rank
    from MyTable
    where City like '%a%'
) a
order by Rank

Answer 2

I suppose that what you mean by "but I want to give more priority to name" is that you want to get the entities with "a" in Name first, and then the other ones (with "a" in City). The respective Linq-to-SQL query would be:

Entities
.Where(e => e.Name.Contains("a") || e.City.Contains("a"))
.OrderByDescending(e => e.Name.Contains("a"));

Answer 3

An SQL version of Dan Dumitru's solution would be:

SELECT Id, Name, City
FROM   cities
WHERE Name LIKE 'b%'
   OR City LIKE 'b%'
ORDER BY
   Name LIKE 'b%' DESC

With some example data:

Id | Name | City
1  | aa   | bb
2  | bb   | aa

This gives:

Id  Name    City
2   bb      aa
1   aa      bb

Whereas

SELECT Id, Name, City
FROM   cities
WHERE Name LIKE 'b%' 
   OR City LIKE 'b%'
ORDER BY
    Name, City

Gives the incorrect:

Id  Name  City
1   aa    bb
2   bb    aa

That's because in the first we sorted by which field matched, but in the second we sorted by field values.