Avatar of dtechfish
dtechfish
Flag for United States of America 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
Microsoft SQL ServerASP.NETSQL

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
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....
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question