May 1, 2013 at 6:48 am
I have two tables, one Master, one Detail. I'm trying to build a query to get the top 2 products by quantity but can't wrap my head around it.
Any help is greatly appreciated.
DECLARE @MasterTable TABLE
(
MasterId int,
Descript varchar(100)
)
DECLARE @DetailTable TABLE
(
DetailId int,
DetailDescript varchar(100),
ProductQty int,
MasterId int
)
INSERT INTO @MasterTable VALUES (1, 'Descript 1')
INSERT INTO @MasterTable VALUES (2, 'Descript 2')
INSERT INTO @DetailTable VALUES (1, 'Descript Detail 1', 35, 1)
INSERT INTO @DetailTable VALUES (2, 'Descript Detail 2', 25, 1)
INSERT INTO @DetailTable VALUES (3, 'Descript Detail 3', 15, 1)
INSERT INTO @DetailTable VALUES (4, 'Descript Detail 4', 12, 2)
INSERT INTO @DetailTable VALUES (5, 'Descript Detail 5', 43, 2)
INSERT INTO @DetailTable VALUES (6, 'Descript Detail 6', 17, 2)
SELECT * FROM @MasterTable mt
INNER JOIN @DetailTable dt ON mt.MasterId = dt.MasterId
It should return :
MasterId DetailId ProductQty
1 _ _ _ _ 1 _ _ _ 35
1 _ _ _ _ 2 _ _ _ 25
2 _ _ _ _ 5 _ _ _ 43
2 _ _ _ _ 6 _ _ _ 17
Thanks
May 1, 2013 at 7:05 am
You can use the row_number() function. Here is one way of doing it:
WITH MyCTE AS (
select MasterId, DetailId, ProductQty, ROW_NUMBER() OVER (PARTITION BY MasterId ORDER BY ProductQty desc) as RowNum
FROM @DetailTable)
select MasterId, DetailId, ProductQty
from MyCTE
where RowNum <= 2
order by MasterId, RowNum
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 1, 2013 at 8:23 am
It works, thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply