Nick Nick - 6 months ago 17
SQL Question

Select row by priority of partial values, using T-SQL

I have a database table (schema below):

Items
------
ItemId
UserId
FullURL


I want to select one row from it based on two criteria:


  1. either has a given partial URL or given domain name in full URL field, but prioritize for partial URL.

  2. similarly, prioritize for a given UserId, else return for any user.



I came up with this basic query (trying to do it in a single query), but it doesn't really prioritize the rows as I want:

SELECT TOP 1 *
FROM Items
WHERE (FullURL LIKE '%website.com/product-132873%'
OR FullURL LIKE '%website.com%')
AND (UserId = 1 or UserId > 0)


The ideal optimized query should return one row that matches (in order of precedence):


  • FullURL LIKE '%website.com/product-132873%' (i.e. partial URL) AND UserId=1 (i.e. given user), else

  • FullURL LIKE '%website.com/product-132873%' (i.e. partial URL) AND UserId>0 (i.e. any user), else

  • FullURL LIKE '%website.com%' (i.e. domain) AND UserId=1 (i.e. given user), else

  • FullURL LIKE '%website.com%' (i.e. domain) AND UserId>0 (i.e. any user).



Hope it makes sense.

I'll appreciate your suggestions.

Answer

Order by expression which gives the priority you need

SELECT TOP 1 * FROM Items WHERE
(FullURL LIKE '%website.com/product-132873%' OR FullURL LIKE '%website.com%') AND
(UserId = 1 or UserId > 0)
ORDER BY 
 CASE WHEN FullURL LIKE '%website.com/product-132873% THEN 0  ELSE 2 END 
 + CASE WHEN UserId=1 THEN 0 ELSE 1 END