select top 4 in each category (categories complex :( )

[Fekla]

Hi sql wizards,

I am having trouble figuring out the sql syntax. This is the diagram of my database.

What I am trying to do is to select the top 4 points for each UNIT in each EVENT (actually it should be top 2 in each course) and I cant compose it.
i ve arrived at this so far:


SELECT E2R.EventID, E2R.Points, Runners.RunnerID, Runners.Surname, Runners.UnitID
FROM Runners INNER JOIN EventRunner AS E2R ON Runners.RunnerID = E2R.RunnerID
WHERE (((E2R.Points) In (

SELECT TOP 4 EventRunner.EventID, EventRunner.RunnerID, EventRunner.Points, Runners.UnitID
FROM Units INNER JOIN (Runners INNER JOIN EventRunner ON Runners.RunnerID = EventRunner.RunnerID) ON Units.UnitID = Runners.UnitID
WHERE EventRunner =e2r.runnerId AND Runner.unitID=runners.unitID

))
);

Password is 111

Thanks in advance!

 

[Bilbo_Baggins_Esq]

your attached db is password protected so I can't look at it.

 

[Fekla]

i m so forgetful! its 111