SQL Query Help

dtechfish
dtechfish used Ask the Experts™
on
We are having a vegetable growing contest.  All vegetable weights are going to be entered into a sql db.  Each competeing team can enter as many vegetables of the same type as they want.  Only their heaviest two vegetables will count in each vegetable type category. Some teams may have only entered one vegetable. Each vegatable type will have its own category.  I am trying to figure out how to create a query that will only pull the top two heaviest vegetables of the same type for each team and then sort them by the individual weight of the vegetables.  The heaviest vegetable wins but a teams heaviest two veggetable will count.  A team could place 1st and second but cannot take 1st, 2nd, 3rd.

TABLE VEGGIE
veggieID
veggieType
teamID
veggieWeight
veggieTimeDate

TABLE TEAM
teamID
teamName

I know the following is not correct but may help in explaining the query details...

SELECT teamID, teamName, ...Top 2 Heaviest Vegetables by this team..., veggieWeight, veggieTimeDate
FROM VeggieTable INNER JOIN
TeamTable ON TeamTable.teamID = VeggieTable.teamID
WHERE veggieType = @veggieType
ORDER BY veggieWeight DESC
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
looks like homework...

we can't give you the answer but help

try using row_number()  or dense_rank() to assign values to each row

for example...

row_number() over(partition by veggietable.teamid order by veggieweight desc)

Author

Commented:
HAHAHA....Certainly not homework...lol...just a substitution of objects because we are bound by an NDA...I'll look into your suggestions.

Author

Commented:
I don't really understand the row_number and could use a little more direction.

I tried the following:

SELECT        V1.veggieID, V1.teamID, V1.veggieWeight, T1.teamName
FROM            Veggie AS V1 INNER JOIN
                         Team AS T1 ON V1.teamID = T1.teamID
WHERE        (V1.teamID IN
                             (SELECT        TOP (2) V2.teamID
                               FROM            Veggie AS V2 INNER JOIN
                                                         Team AS T2 ON V2.teamID = T2.teamID
                               WHERE        (V2.teamID = F1.teamID)
                               ORDER BY V2.veggieWeight DESC))
ORDER BY V1.veggieWeight DESC

The result set included all veggies entered where I need it to only include the greatest two from each team.

Please Help....
Most Valuable Expert 2011
Top Expert 2012
Commented:
don't use top, that applies to the entire record set
row_number applies to groups (partitions) which is what you're looking for


SELECT *
  FROM (SELECT veggie.*,
               team.*,
               ROW_NUMBER() OVER (PARTITION BY veggie.teamid ORDER BY veggie.veggieweight DESC) rn
          FROM veggie INNER JOIN team ON veggie.teamid = team.teamid) as x
 WHERE rn <= 2
ORDER BY veggieweight DESC


after using row_number to get the biggest 2 per team, you could use top to get the 3 biggest over all,

I wrote an article that shows several uses for analytics with somewhat similar requirements.  Most of the syntax is compatible with sql server with the exception of ROWNUM (different than ROW_NUMBER) but that's where TOP comes in.

Hopefully it'll help you see other possibilities for these functions

http://www.experts-exchange.com/A_9869.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial