samedi 23 janvier 2016

SQLite: Subquery to get random value with condition for each row

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