In BIGQUERY,With this table i try to filter the value to get the team where first, the second and the third at the last stage ...
season |stage |name |team | team_point | ranking |
----------------------------------------------------------------------------
2008 |1 |Italy Serie A |Genoa | 1 | 3 |
2008 |1 |Italy Serie A |Milan | 3 | 1 |
2008 |1 |Italy Serie A |Udinese | 3 | 2 |
2008 |1 |Italy Serie A |Juventus | 0 | 5 |
2008 |1 |Italy Serie A |Naples | 1 | 4 |
2008 |2 |Italy Serie A |Genoa | 4 | 2 |
2008 |2 |Italy Serie A |Milan | 6 | 1 |
2008 |2 |Italy Serie A |Udinese | 3 | 3 |
2008 |2 |Italy Serie A |Udinese | 3 | 2 |
2008 |2 |Italy Serie A |Juventus | 0 | 5 |
2008 |2 |Italy Serie A |Naples | 1 | 4 |
............................................................................
2008 |38 |Italy Serie A |Genoa | 45 | 5 |
2008 |38 |Italy Serie A |Milan | 76 | 3 |
2008 |38 |Italy Serie A |Juventus | 81 | 1 |
2008 |38 |Italy Serie A |Naples | 80 | 2 |
............................................................................
i want this output.
season |stage |name |team | team_point | ranking |
----------------------------------------------------------------------------
2008 |1 |Italy Serie A |Milan | 3 | 1 |
2008 |1 |Italy Serie A |Juventus | 0 | 5 |
2008 |1 |Italy Serie A |Naples | 1 | 4 |
2008 |2 |Italy Serie A |Milan | 6 | 1 |
2008 |2 |Italy Serie A |Juventus | 0 | 5 |
2008 |2 |Italy Serie A |Naples | 1 | 4 |
............................................................................
2008 |38 |Italy Serie A |Milan | 76 | 3 |
2008 |38 |Italy Serie A |Juventus | 81 | 1 |
2008 |38 |Italy Serie A |Naples | 80 | 2 |
............................................................................
I try this query with clause "where" but it's not good because the where clause applies to all rows while I would like it to apply to the max(stage)
SELECT
season,
stage,
name,
team,
team_point,
ranking
FROM
TABLE
WHERE
ranking <= 3
I think i have to put a condition on the"Where" to choose the ranking at the last stage and keep the values for this teams before the last stage...
Try:
WITH a AS (
SELECT
team
FROM
TABLE
ORDER BY stage DESC, ranking
LIMIT 3
)
SELECT
season,
stage,
name,
team,
team_point,
ranking
FROM
TABLE
WHERE team in (SELECT team FROM a)