SELECT c.ROWI AS contest_id, TEAMS.KIND AS kind,
DENSE_RANK() OVER (PARTITION BY c.ROWI, TEAMS.KIND ORDER BY p.MARK DESC) AS res,
TEAMS.ROWI AS team_id FROM CONTESTS c
JOIN POINTS p, TEAMS ON p.CONTEST = c.ROWI AND p.TEAM  = TEAMS.ROWI
ORDER BY contest_id, kind, res, team_id;