Selecting one record from group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting one record from group

Author

Topic

roblasch
Starting Member

10 Posts

Posted - 2002-09-01 : 12:52:20

I have found a few solutions to this question, but none seem to work in this instance. I have a search query that returns these results( they could be in a temp table or derived)

Weight AdvertiserID CategoryID
10 1 1
6 1 2
8 1 3
4 1 1
10 2 1
8 2 2

Any of the three columns can be any value. I need to total the weight for each categoryID,AdvertiserID and then select the top record for each advertiserID. The column I need to access is the CategoryID. In other words, the search returns multiple categories for each advertiser and I need to select the one with the highest weight.

Thanks, Robert Lasch

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-09-01 : 17:15:53

I will use a view to simplify the query. If you don't like views, you can substitute it with a derived table, but it only makes the query look messy. Anyways, here's my solution:

--Create the view first

CREATE VIEW AdsV AS
SELECT AdvertiserID, CategoryID, SUM(Weight) AS Weight
FROM Ads
GROUP BY AdvertiserID, CategoryID

--Now query the view
--to get the highest ranked category
--for each advertiser

SELECT v1.AdvertiserID, v1.CategoryID, v1.Weight
FROM AdsV v1
JOIN
(
SELECT AdvertiserID, MAX(Weight) AS Weight
FROM AdsV
GROUP BY AdvertiserID
) AS v2
ON v1.AdvertiserID = v2.AdvertiserID AND v1.Weight = v2.Weight

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources