SQL Server Performance Forum – Threads Archive
Complicate query
I have done some different queries before but im lost on how to do this one…I am needing to show ToP 2 "OF" each grouping Only way I could do it was in a RecordSet Loop
20 Teams, each team can have many, many members
For each of the 20 teams, I need to show the top 2 Members based on counts. sql="Select TeamName From Teams order by TeamName" Do while not rs.EOF
strTEAMNAME = rs("TeamName") sql2="SELECT TOP 2 Tourneys.Tourney, Members.Team, &_ Members.MemberName, Count(Reports.Win) AS CountOfWin, Count(Reports.Loss) AS CountOfLoss
FROM Tourneys INNER JOIN (Members INNER JOIN Reports ON &_ Members.ID = Reports.MemID) ON Tourneys.ID = Reports.TourID
GROUP BY Tourneys.Tourney, Members.Team, Members.MemberName
HAVING (((Tourneys.Tourney)=19) AND
((Members.Team)=’"& strTEAMNAME &"’))
ORDER BY ( (Count(Reports.Win)*5) + Count(Reports.Loss)) DESC" Response.Write rs2("MemberName") etc… rs.MoveNext
Loop ‘obviously this is 20 queries which is not good, only shoing an example. This Outputs (as I need it to)
TeamAA, AARussell
TeamAA, AABill
TeamBB, BBRob
TeamBB, BBAndre
TeamCC, CCRalf
TeamCC, CCFred
etc…. I have tried to combine the 2 queries before a few different ways
But seems that using Top 2 inside query would only output a total of 2 records only
not the 20 teams x 2 records.
Or when I add the Count(Win) and Count(loss) then with all the groupby clauses that are required, I end up with no records found…
Im really stuck on seeing this one. sqlServer 2000
Any thoughts? Thanks in advance.
what you want is a correlated subquery
this would work better if you had a Team table from which we could get a list of the distinct teams
and Members had a TeamID, instead of Team see the query below, it joins Team to Members, which by itself, would list all the members for each team,
but if you use the WHERE clause below, it forces the Members ID to be one of the top 2 for each team ID
you will need to do the rest yourself for your regarding the win loss and tourney stuff SELECT t.Team, m.MemberName
FROM Team t
INNER JOIN Members m ON m.TeamID = t.ID
WHERE m.ID IN ( SELECT TOP 2 m2.ID
FROM Members m2
WHERE m2.TeamID = t.ID )
HI And thanks for your reply. I do have a table for teams and members.
Teams.TeamName on Members.Team is the joined field.
(It is an indexed 3 char field)
It was set up like this to save joining the teams table in many cases just to display the teams 2 letter name. This looks like what I am needing.
I will try my best to modify it to use the Count’s on those fields. Ill post back if i have any problems.
Thanks very much for your response. Russell M
]]>