TOP n in a group problem
-
I am having some brain strain trying to figure out how to do this. I have a table of shippers, destinations, and shipping weights. I am trying to figure out how to determine the top n shippers to each destination. For example, in my data set below, John and Fred are the top 2 shippers by weight to LAX and Fred and Irv are the top 2 shippers to O'Hare. How can I construct a query that will give me the top n shippers in each group? Thanks.
declare @Table1 TABLE (RowID int IDENTITY(1, 1), Shipper varchar(15), Dest varchar(15), Weight int)
INSERT INTO @Table1 VALUES ('Fred', 'LAX', 100)-- Fred ships 2d most weight to LAX
INSERT INTO @Table1 VALUES ('Fred', 'LAX', 200)
INSERT INTO @Table1 VALUES ('Fred', 'LAX', 100)
INSERT INTO @Table1 VALUES ('John', 'LAX', 500)-- John ships most weight to LAX
INSERT INTO @Table1 VALUES ('John', 'LAX', 500)
INSERT INTO @Table1 VALUES ('John', 'LAX', 500)
INSERT INTO @Table1 VALUES ('Irv', 'LAX', 50)
INSERT INTO @Table1 VALUES ('Irv', 'LAX', 50)
INSERT INTO @Table1 VALUES ('Irv', 'LAX', 50)
INSERT INTO @Table1 VALUES ('Irv', 'ORD', 50)
INSERT INTO @Table1 VALUES ('Irv', 'ORD', 50)
INSERT INTO @Table1 VALUES ('Irv', 'ORD', 50)
INSERT INTO @Table1 VALUES ('John', 'ORD', 50)-- John ships most weight to ORD
INSERT INTO @Table1 VALUES ('John', 'ORD', 50)
INSERT INTO @Table1 VALUES ('Fred', 'ORD', 100)
INSERT INTO @Table1 VALUES ('Fred', 'ORD', 100)
INSERT INTO @Table1 VALUES ('Fred', 'ORD', 100)
SELECT a.Shipper, a.Dest, SUM(a.Weight) AS SumWeight FROM @Table1 AS a
GROUP BY Dest, Shipper
ORDER BY Dest, SumWeight DESC
Shipper Dest SumWeight
--------------- --------------- -----------
John LAX 1500
Fred LAX 400
Irv LAX 150
Fred ORD 300
Irv ORD 150
John ORD 100
There is no "i" in team, but idiot has two. -
brain strain is right
- here's one solution that I learned from a member called Vladan loooong ago...SELECT Shipper, Dest, SUM(Weight)
FROM @Table1 A
WHERE Shipper IN (SELECT TOP 2 Shipper FROM @Table1 B WHERE A.Dest = B.Dest
GROUP BY Dest, Shipper ORDER BY SUM(Weight) DESC)
GROUP BY Dest, Shipper
ORDER BY Dest, SUM(Weight) DESC
The other one would be to use your select and insert all values into a temp table and then query the temp table: like so...
SELECT a.Shipper, a.Dest, SUM(a.Weight) AS SumWeight
INTO #TEST
FROM @Table1 AS a
GROUP BY Dest, Shipper
ORDER BY Dest, SumWeight DESC
SELECT * FROM #TEST A
WHERE Shipper IN
(SELECT TOP 2 Shipper FROM #TEST B where A.Dest = B.Dest)
**ASCII stupid question, get a stupid ANSI !!!** -
sushila, Hey little girl it's past your bed time. Up so late and thinking so well should be aganist the law.
Mike
-
- darn but this t-sql stuff is so addictive!
**ASCII stupid question, get a stupid ANSI !!!** -
It is addictive
the only problem is that once again I can not stay and play.
I just poped in to see if I could find someone with a problem I could help with without investing a lot of time. You know just a 15 min quickey. This looked like a good candidate as I have the answer from the same source as you. But your flying fingers beat me to the punch.so once again it is back to the grind stone for me.
Enjoy
Mike
-
Thanks so much, sushila. I knew there was a correlated subquery in there somewhere, but couldn't figure it out.
There is no "i" in team, but idiot has two. -
anytime!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply