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