Here is my problem. I have a select statement something like this:
select * from mytable where mycol like '%finance%'
The results look something like this:
id mycol
16 the finance department
8 i like the finance people
43 chief finance officer
22 finance
68 finance trading
I would like to have the order come out like so:
id mycol
22 finance
68 finance trading
43 chief finance officer
16 the finance department
8 i like the finance people
In essence, to have the select query use:
where mycol = 'finance'
then
where mycol = 'finance%'
then
where mycol = '%finance%'
1) I cannot use the id column for ordering purposes.
2) My query is far more complex than this (it's part of a paging a recordset on a large db using SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY t.category, t.sequence) ...
I'm thinking I might need to use UNION or RANK() or something, but I'm getting out of my league now. Any guidance greatly appreciated :)
One obvious solution:
Select Id, mycol
, Case
When mycol = 'finance' Then 1
When mycol Like 'finance%' Then 2
When mycol Like '%finance%' Then 3
End As Rnk
From MyTable
Where mycol Like '%finance%'
Order By Rnk
Granted, this won't perform well with the leading and trailing wildcard. Another way would be to use full-text search which will give you a relevancy rank.