Hello SQL-knowing chums!
I'm banging my head against a wall on this one. I'm getting close, but it's just not giving me the right answer.
Gaywood and Grim... have been doing a load of marathons lately! They've stored all their race information in this table, 'races':
Racer | RaceDate | Position | Completed
Grim... 10/3/16 4 Y
Grim... 12/3/16 4 Y
Grim... 1/2/16 1 Y
Grim... 10/5/16 NULL N
Grim... 5/4/16 6 Y
Gaywood 6/4/16 NULL N
Gaywood 17/1/16 2 Y
Gaywood 10/9/16 4 Y
Gaywood 4/8/16 4 Y
Gaywood 10/3/16 4 Y
Now, they're trying to get some friends to join in. So they've got another table too for all their friends, 'friends'. Most of their friends haven't done any races yet.
Now, they're pretty competitive, so they only care about the most recent results. Also they want to annoy their friends who haven't joined in yet. So this is the output they want:
Racer | Position | Completed
Grim... NULL N
Gaywood 4 Y
Curiosity NUL:L NULL
Bobbyaro NULL NULL
This is proving challenging. Lots of having to do groupings, but then the groupings bugger up the filtering by most recent, etc. This is what I've got so far:
select f.Racer, r.Position, r.Completed
from (select Racer, MAX(Races.[RaceDate]) as RecentRace, Position, Completed
from races) group by races.Racer, Races.Position, Races.Completed) as r
right outer join friends as f
group by f.Racer, r.Position, r.Completed
It's horridly complicated, probably wildly inefficient, and it still doesn't bloody work properly. Thoughts?