The title is not very clear I hope to describe my problem better here - feel free to suggest me a better title.
I have a query that returns me a table of team and scores:
Team Scores
-------------
Team1 2
Team2 4
Team3 2
Team4 14
Team5 9
Team6 2
Team7 1
Team8 11
Team9 7
For each team I'd like to create three additional columns selecting distinct teams within Team1 - Team9 with a score lower that the team considered. For that purpose we limit ourself here to scores > 2. This could be a possible result:
Team Scores Res1 Res2 Res3
------------------------------
Team2 4 Team1 Team6 Team7
Team4 14 Team9 Team7 Team8
Team5 9 Team6 Team9 Team2
Team8 11 Team1 Team9 Team6
Team9 7 Team2 Team1 Team6
Say the table is called T so far I have try this which doesn't work for one additional column:
Select T.score, T.team, (Select tea as Res1 (Select T.team as tea, T.score as sco from T where sco < T.score order by random()) limit 1) from T;
Aucun commentaire:
Enregistrer un commentaire