BIGQUERY : value filter with where and condition

David Published at Dev

David

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...

99_m4n

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)

Collected from the Internet

Please contact [email protected] to delete if infringement.