Is this a union query problem/solution? 1

[TrollBro]

Is there an easy way to do this in a union qry?

I am trying to create a qry on a single table to select the Captain from the Team based on Goals, and then associate that result with each record. Below illustration makes the point clearer I hope. Is this doable? Any help would be much appreciated. (table would have 100k+ records)
Thanks

Table:
team, player, Goals
T1, P1,12
T1, P2,7
T1, P3, 4
T2, P4, 11
T2, P5, 18

Desired Qry results:
team, player, Goals, Captain,
T1, P1,12, P1
T1, P2,7, P1
T1, P3, 4, P1
T2, P4, 11, P5
T2, P5, 18, P5

 

[PHV]

SELECT A.team, A.player, A.Goals, B.player AS Captain
FROM Table AS A INNER JOIN (
SELECT team, Max(Goals) AS MaxGoals FROM Table GROUP BY team
) AS B ON A.team = B.team AND A.Goals = B.MaxGoals

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TrollBro]

Thanks PHV

I get an error: enter parameter value b.player

Am I missing something? Thanks

here is my code:

SELECT A.[team], A.[player], A.[Goals], B.[player] AS Captain
FROM [testtbl] AS A INNER JOIN (
SELECT [team], Max([Goals]) AS MaxGoals FROM [testtbl] GROUP BY [team]
) AS B ON A.[team] = B.[team] AND A.[Goals] = B.[MaxGoals];

 

[TrollBro]

PHV

This is the best I could do and seems to give me the correct results. I also used alpha sort (min) player name as a tie breaker (my player values lead with a numeric ranking number - somewhat arbitrary but directionally accurate for my purposes). Not very eloquent, but seems to work so far. Thanks

SELECT a.team, a.name, a.player, a.goals, c.goals, Min(c.player) AS Captain
FROM testtbl AS a INNER JOIN ([SELECT [team], Max([Goals]) AS MaxGoals FROM [testtbl] GROUP BY [team]
]. AS B INNER JOIN testtbl AS c ON (B.team = c.team) AND (B.MaxGoals = c.goals)) ON a.team = B.team
GROUP BY a.team, a.name, a.player, B.team, a.goals, B.MaxGoals, c.team, c.goals
ORDER BY a.team;

 

[PHV]

OOps, sorry for the wrong code ...

SELECT A.team, A.player, A.Goals, B.player AS Captain
FROM testtbl AS A
(INNER JOIN (SELECT team, Max(Goals) AS MaxGoals FROM testtbl
GROUP BY team) AS M ON A.team = M.team)
INNER JOIN testtbl AS B ON M.team = B.team AND M.MaxGoals = B.Goals

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TrollBro]

Thanks PHV - I did a direct cut&paste but this generates an error message "syntaxt error in From clause" and I can't figure out what's causing it. any ideas?

Thanks

 

[PHV]

sorry for the typo

SELECT A.team, A.player, A.Goals, B.player AS Captain
FROM (testtbl AS A
INNER JOIN (SELECT team, Max(Goals) AS MaxGoals FROM testtbl
GROUP BY team) AS M ON A.team = M.team)
INNER JOIN testtbl AS B ON M.team = B.team AND M.MaxGoals = B.Goals

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TrollBro]

Thanks PHV - this worked perfect right out of the box!