Help with search results query

[- TW]

I need some help with a query. I have a table with inventory that I
need to allow customer searches on. Based on their search criteria, a
preference level is calculated; the higher the preference level, the
higher on the order on the search results.

The hard part is when the results are supposed to be limited to a
maximum number of stores and items. Let's say that they only want to
see 3 stores and a max of 5 items per store. What needs to be
returned is the 3 stores with the best Preference and the 5 best items
at each store.

Create Table Inventory( StoreId int, ItemId int, Preference int )

[David Portas]

Re: Help with search results query

Loads of questions that your spec leaves unanswered: What is the primary key
of this table? Is Preference unique? Is (itemid, preference) unique or
(storeid, preference) unique? What if multiple items/stores have the same
value for Preference? Could there be more than X items with the same
preference (where X is the number of items required)? How do you decide
which are the "top" stores: by SUM(preference) for the storeid; or
SUM(preference) for the "top" X items; or some other method?

Here's a best guess:

CREATE TABLE Inventory (storeid INTEGER, itemid INTEGER, preference INTEGER
NOT NULL, PRIMARY KEY (storeid, itemid))

DECLARE @no_stores INTEGER, @no_items_per_s tore INTEGER

SET @no_stores = 3
SET @no_items_per_s tore = 5

SELECT I1.storeid, I1.itemid, I1.preference
FROM Inventory AS I1
LEFT JOIN Inventory AS I2
ON I1.storeid = I2.storeid
AND I1.preference < I2.preference
JOIN
(SELECT I1.storeid
FROM
(SELECT storeid, SUM(preference)
FROM Inventory
GROUP BY storeid)
AS I1 (storeid,prefer ence)
LEFT JOIN
(SELECT storeid, SUM(preference)
FROM Inventory
GROUP BY storeid)
AS I2 (storeid,prefer ence)
ON I1.preference < I2.preference
GROUP BY I1.storeid
HAVING COUNT(I2.prefer ence) < @no_stores) AS S
ON I1.storeid = S.storeid
GROUP BY I1.storeid, I1.itemid, I1.preference
HAVING COUNT(I2.storei d) < @no_items_per_s tore

--
David Portas
------------
Please reply only to the newsgroup
--