osiris355 osiris355 - 3 months ago 9
SQL Question

SQL Query search that ranks one where clause over the other

I would like to to allow a user to search based on two different fields in the same table (target and name). However, if there is an exact match on target i would like that to take precedence on the order by so a row with dis as the target shows up first.

An help would be appreciated.

select
Id
,[target]
,[name]
,[extrafield1]
,[extrafield2]
,[extrafield3]
From [inventory]
where [target] LIKE 'dil%' OR [name] LIKE '%dil%'

Answer

Don't use a widlcard on both sides of your string ie. %string% This will cause a serial read of the table. Put it on just the right side.

select 
    Id 
    ,[target] 
    ,[name] 
    ,[extrafield1]
    ,[extrafield2]
    ,[extrafield3]
    ,case when [target] = 'dil' or [name] = 'dil' then 1 else 2 end as rank -- to display your ranking
From [inventory]
where [target] LIKE 'dil%' or [name] like 'dil%'   
order by case when [target] = 'dil' or [name] = 'dil' then 1 else 2 end