troubleshooting Question

SQL Query Help

Avatar of dtechfish
dtechfishFlag for United States of America asked on
Microsoft SQL ServerASP.NETSQL
4 Comments1 Solution393 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros