September 19, 2009 at 3:27 am
Hi,
I've got the following query that uses a full-text index to search for the TOP 5 products (from the RawProducts table) matching the query, in a given Shop (populated by the @ShopId variable).
At the moment I'm calling this procedure over and over again for every ShopId (there are 27 Shops) - which is a bit slow.
My question is - could anyone let me know how to modify the query to accept, say, a comma-separated list of ShopIds in the @ShopId variable, and to return the TOP 5 matches from each shop?
Here's the query so far:
DECLARE @ShopId uniqueidentifier
SET @ShopId = '49506541-4ce2-40ac-812a-7ab262e6f0b0'
SELECT TOP 5
ftt.RANK,
rp.*
FROM
RawProducts rp
INNER JOIN
CONTAINSTABLE
(
RawProducts,
RawProductName,
'ISABOUT("*radox*","*shower*")'
)
AS ftt
ON
ftt.=rp.RawProductId
WHERE
rp.ShopId = @ShopId
ORDER BY
ftt.RANK DESC
Really appreciate your help! 🙂
thanks!
September 19, 2009 at 9:50 am
Have a look at the DENSE_RANK function I used. All the rest is just a quick & dirty code sample. Please note that I haven't tested this but it should do the job...
DECLARE @ShopIdCSV NVARCHAR(MAX)
SET @ShopIdCSV = '49506541-4ce2-40ac-812a-7ab262e6f0b0,49506541-4ce2-40ac-812a-7ab262e6f0b1,49506541-4ce2-40ac-812a-7ab262e6f0b2'
--This is a very quick & dirty way to
--convert CSV list to table.
--I usually use a function that does that
--using XML, but that's for another post...
--It works here because it's a GUID. I never use that in real production code...
DECLARE @ShopList TABLE (ShopID uniqueidentifier)
SET @ShopIdCSV = ',' + @ShopIdCSV + ','
INSERT @ShopList (ShopID)
SELECT DISTINCT RawProducts.ShopID
FROM
RawProducts
WHERE
@ShopIdCSV LIKE '%,' + CAST(RawProducts.ShopID as VARCHAR(36)) = ',%'
;WITH Q AS
(
SELECT
DENSE_RANK() OVER (PARTITION BY RawProducts.ShopID ORDER BY ftt.RANK) AS RankInShop,
ftt.RANK,
rp.*
FROM
RawProducts rp
INNER JOIN
CONTAINSTABLE
(
RawProducts,
RawProductName,
'ISABOUT("*radox*","*shower*")'
)
AS ftt
ON
ftt.=rp.RawProductId
INNER JOIN @ShopList SL ON
RawProducts.ShopID = SL.ShopID
)
SELECT
* FROM
Q
WHERE
RankInShop <=5
ORDER BY
ShopID, RankInShop
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply