asked on

SQL Query Help

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

Sean Stuber

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)

dtechfish

ASKER

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

dtechfish

ASKER

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....

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial