CODEWITHSUNDEEP
sqlsearch
Leigh
Leigh

Reputation: 1533

Select highest ranking category SQL

I have a temp table inside an SQL sproc (used for searching), the columns are ProductID, CategoryID and WeightRank.

From this table I need to get the CategoryID with the highest WeightRank. I am struggling to figure the syntax, any help would be kindly appreciated.

Note: the temp table has many products with the same CategoryID, I need to find the combined WeightRank for each unique CategoryID and return just [highest ranking] CategoryID. (Hope this makes sense?)

Upvotes: 1

Views: 516

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52675

Standard SQL solution allowing for multiple categories that tie for max

SELECT *
FROM 
table  t
INNER JOIN 
(
     SELECT
              SUM( WeightRank) weightRank, category_id 
    FROM 
       table t
    GROUP  by
       category_id 
) sumRank
ON t.category_id  = sumrank.category_id 
INNER JOIN 

SELECT  MAX(weightRank) weightRank
FROM (     
    SELECT
              SUM( WeightRank) weightRank, category_id 
    FROM 
       table t
    GROUP  by
       category_id 
) maxRank

ON sumRank.weightRank= maxRank.weightRank

Upvotes: 0

dotjoe
dotjoe

Reputation: 26940

select top 1 categoryID
from #t
group by categoryID
order by sum(WeightRank) desc

or maybe avg(WeightRank)?

Upvotes: 1

Related Questions