SQL Select searching precedence
I was wondering if it is possible to write a SQL SELECT query with searching precedence (I have looked at the typical precedence, AND before OR etc but that doesn't do what I want it do).
Below is a basic query that is basis for the overall query:
开发者_如何学GoSELECT TOP 15 * FROM MyTable WHERE reference LIKE '123%' OR first_name LIKE 'Pa%';
P.S. reference is not the PK, its just a value associated with a person
Now what I am hoping someone could help me with is, I want it first to fill up the top 15 with those that match '123%' and then what ever space is left over with the those that match the first_name 'Pa%'. If I run this query in my code it fills the TOP 15 with those that match 'Pa%' now I am assuming it is because of the way it searches through the database (it finds 15 matches to 'Pa%' before it matches the reference), so is there a way to tell the query to first look for '123%' in reference then when its found or not found any values search 'Pa%' in first_name or would I have to write to separate query and merge there results?
~Thank you in advance :D
Or even simpler:
SELECT TOP 15 *
FROM MyTable
WHERE reference LIKE '123%' OR first_name LIKE 'Pa%'
ORDER BY reference LIKE '123%' DESC;
true
naturally orders after false
, so just order by the match you want + DESC
SELECT TOP 15 *
FROM MyTable
WHERE reference LIKE '123%' OR first_name LIKE 'Pa%'
ORDER BY
CASE WHEN reference LIKE '123%' THEN 1
WHEN first_name LIKE 'Pa%' THEN 2 END ASC
You can 'order by' a numeric projection of you search criteria
SELECT TOP 15 * FROM MyTable WHERE reference LIKE '123%' OR first_name LIKE 'Pa%'
order by case when reference LIKE '123%' then 100 else 0 end
+ case when first_name LIKE 'pa%' then 1 else 0 end
but it is not a performance booster ;-)
精彩评论