【练习篇】SQLZOO 习题答案_Window functions

General Elections were held in the UK in 2015 and 2017. Every citizen votes in a constituency. The candidate who gains the most votes becomes MP for that constituency.

All these results are recorded in a table ge

Contents
1 Warming up
2 Who won?
3 PARTITION BY
4 Edinburgh Constituency
5 Winners Only
6 Scottish seats

1.Show the lastName, party and votes for the constituency ‘S14000024’ in 2017.

SELECT lastName, party, votes
  FROM ge
 WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY votes DESC

2.You can use the RANK function to see the order of the candidates. If you RANK using (ORDER BY votes DESC) then the candidate with the most votes has rank 1.

Show the party and RANK for constituency S14000024 in 2017. List the output by party

SELECT party, votes,
       RANK(