开发者

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:

开发者_如何学Go
SELECT 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 ;-)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜