sql - How can I get my row that is most exact be the first one? -

i have search field user search in case "rg transport". want exact matches first.

this generated query:

select distinct department.bold_id,                  departmentid + ' ' + organization.name + ' - '                  + department.name displayvalue,                  organization.name    organization,         department   organization.bold_id = department.organization         , upper(searchkeysname) '%[[]%rg%]%'         , upper(searchkeysname) '%[[]%transport%]%'  order  organization.name  

"rg transport ab" on row 78. want on first row. try union

select distinct department.bold_id, departmentid + ' ' + organization.name + ' - ' + department.name displayvalue,  organization.name  department, organization organization.bold_id = department.organization  , upper(searchkeysname) '%[[]rg transport%]%'  union select distinct department.bold_id,                  departmentid + ' ' + organization.name + ' - '                  + department.name displayvalue,                  organization.name    organization,         department   organization.bold_id = department.organization         , upper(searchkeysname) '%[[]%rg%]%'         , upper(searchkeysname) '%[[]%transport%]%'  

it works fine following rows not sorted on name. if add

order organization.name

to last query first row lost. there other way can ?

simply add column 1 in top half of union , column 2 in bottom half

your order becomes

order newcolumn, organization.name 

it query may return company twice

so perhaps along these lines may more relevant

select weakmatch.bold_id,    weakmatch.displayvalue,    weakmatch.name   (select distinct department.bold_id,                     departmentid + ' ' + organization.name + ' - '                     + department.name displayvalue,                     organization.name       department,            organization      organization.bold_id = department.organization            , upper(searchkeysname) '%[[]rg transport%]%')    directmatch    left outer join (select distinct department.bold_id,                                     departmentid + ' ' + organization.name +                                     ' - '                                     + department.name displayvalue,                                     organization.name                       organization,                            department                      organization.bold_id = department.organization                            , upper(searchkeysname) '%[[]%rg%]%'                            , upper(searchkeysname)                                '%[[]%transport%]%')           weakmatch                 on weakmatch.bold_id = directmatch.bold_id order  directmatch.name,       weakmatch.name  

although assumes directmatch found within weakmatch