I have a search query that is dynamically compiled using NHibernate's criteria query mechanism. The resulting SQL query might look like:
select
*
from
sometable
where
(
(
firstname like 'chris%' or
lastname like 'chris%'
)
and
(
firstname like 'vann%' or
lastname like 'vann%'
)
)
The data in the table might look like:
FirstName LastName
------------------------------
Chris Smith
John Vann
Chris Vann
I'd like to order the results such that a row matching both sub-clauses in the where clause (i.e. firstname = Chris and lastname = Vann) is ranked higher than a row matching only one of the sub-clauses. Is this possible in standard SQL?
Edit: I greatly simplified the question to get down to the guts of the problem.
This is only a point to start. You can create a calculate priority column and sort rows by this column. The column is a indicator for well match row. Here a sample code writed for you:
create table #t (f varchar(10), l varchar(10) );
insert into #t values ('aa','ee'),('aa','ii'),('oo','ee');
select
*,
case when f like 'aa%' then 1 else 0 end +
case when l like 'aa%' then 1 else 0 end +
case when f like 'ii%' then 1 else 0 end +
case when l like 'aa%' then 1 else 0 end
as priority
from #t
order by
priority desc
Results:
f l priority
-- -- --------
aa ee 4
aa ii 4
oo ee 0
For your schema may be something like:
select
*.
case when firstname like 'chris%' and lastname like 'vann%' then 4 else 0 +
case when firstname like 'chris%' and lastname not like 'vann%' then 3 else 0 +
case when firstname not like 'chris%' and lastname like 'vann%' then 3 else 0 +
...
as priority
from
sometable
where
(
(
firstname like 'chris%' or
lastname like 'chris%'
)
and
(
firstname like 'vann%' or
lastname like 'vann%'
)
)
order by priority desc