July 13, 2011 at 9:36 am
Hi, I have the following two tables:
******
ITEMS
******
ItemID__Price
A_____5
B_____6
C_____7
******
MAPPING
******
MappingID__MasterItemID__MappedItemID
1_________A_____________B
I now want to write a query that returns the two lowest priced items: eg A and C (as A is mapped to B)??
Any pointers would be great!
Thanks
Ric
July 14, 2011 at 10:11 am
Try this. Though you really need to give more data to make this a more complete solution:
CREATE TABLE #Items
(
ID INT IDENTITY PRIMARY KEY,
ItemID VARCHAR(5),
Price INT
)
CREATE TABLE #Mapping
(
ID INT IDENTITY PRIMARY KEY,
MasterItemID VARCHAR(5),
MappedItemID VARCHAR(5)
)
INSERT INTO #Items (ItemID, Price)
VALUES ('A', 5)
INSERT INTO #Items (ItemID, Price)
VALUES ('B', 6)
INSERT INTO #Items (ItemID, Price)
VALUES ('C', 7)
INSERT INTO #Mapping (MasterItemID, MappedItemID)
VALUES ('A', 'B')
;
WITH cte AS
(
SELECT
COALESCE(MasterItemID, ItemID) AS NewItemID,
Price,
ROW_NUMBER() OVER (PARTITION BY COALESCE(MasterItemID, ItemID) ORDER BY Price) AS rowNum
FROM #Items
LEFT JOIN #Mapping ON MappedItemID = ItemID
)
SELECT TOP 2 NewItemID, Price
FROM cte
WHERE rowNum = 1
ORDER BY Price
DROP TABLE #Items
DROP TABLE #Mapping
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply