test=*# with result as (
select row_number() over ()
, "gameDay".play_day as "Spieltag"
, player.firstname || ' ' || player.lastname as "Spieler"
, "player_gameDay".total_games
, team.name as "Mannschaft"
, trainer.firstname || ' ' || trainer.lastname as "Trainer"
,extract(year from now())::int-trainer."YearOfBirth" as "Alter"
from "player_gameDay" INNER JOIN player ON "player_gameDay".player_id=player.id
INNER JOIN team ON player.team_id=team.id
INNER JOIN trainer ON team.trainer_id=trainer.id
Inner JOIN "gameDay" ON "player_gameDay".gameday_id="gameDay".id
ORDER BY "Spieltag"
)
select
case when (
select count(*) from result as t1
where t2."Spieltag" = t1."Spieltag"
and t1.row_number <= t2.row_number) = 1
then t2."Spieltag"
else NULL
end as "Spieltag"
, t2.row_number
, t2."Spieltag"
, t2."Spieler"
, t2.total_games
, t2."Mannschaft"
, t2."Trainer"
, t2."Alter"
from result t2
order by t2."Spieltag";
Spieltag | row_number | Spieltag | Spieler | total_games | Mannschaft | Trainer | Alter
----------+------------+----------+---------------+-------------+------------+--------------+-------
1 | 1 | 1 | Hans Muster | 3 | Manschaft1 | Knut Knuston | 48
| 2 | 1 | Sussi Schnell | 3 | Manschaft2 | Hanna Peter | 28
| 3 | 1 | Joana Blatter | 4 | Manschaft2 | Hanna Peter | 28
| 4 | 1 | Jasmin Matter | 1 | Manschaft2 | Hanna Peter | 28
| 5 | 1 | Sepp Müller | 2 | Manschaft1 | Knut Knuston | 48
| 6 | 1 | Hanes Meier | 3 | Manschaft1 | Knut Knuston | 48
2 | 7 | 2 | Sepp Müller | 5 | Manschaft1 | Knut Knuston | 48
| 8 | 2 | Hanes Meier | 1 | Manschaft1 | Knut Knuston | 48
| 9 | 2 | Sabi Süss | 2 | Manschaft3 | Sven Wittwer | 62
| 10 | 2 | Hans Muster | 2 | Manschaft1 | Knut Knuston | 48
| 11 | 2 | Heiri Müller | 4 | Manschaft3 | Sven Wittwer | 62
| 12 | 2 | Josef Gasser | 2 | Manschaft3 | Sven Wittwer | 62
3 | 13 | 3 | Heiri Müller | 2 | Manschaft3 | Sven Wittwer | 62
| 14 | 3 | Josef Gasser | 3 | Manschaft3 | Sven Wittwer | 62
| 15 | 3 | Sussi Schnell | 2 | Manschaft2 | Hanna Peter | 28
| 16 | 3 | Joana Blatter | 3 | Manschaft2 | Hanna Peter | 28
| 17 | 3 | Jasmin Matter | 3 | Manschaft2 | Hanna Peter | 28
| 18 | 3 | Sabi Süss | 3 | Manschaft3 | Sven Wittwer | 62
(18 Zeilen)
test=*#