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

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"));