Window functions
| Language: Project:Language policy | English • 日本語 |
|---|
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
| yr | firstName | lastName | constituency | party | votes |
|---|---|---|---|---|---|
| 2015 | Ian | Murray | S14000024 | Labour | 19293 |
| 2015 | Neil | Hay | S14000024 | Scottish National Party | 16656 |
| 2015 | Miles | Briggs | S14000024 | Conservative | 8626 |
| 2015 | Phyl | Meyer | S14000024 | Green | 2090 |
| 2015 | Pramod | Subbaraman | S14000024 | Liberal Democrat | 1823 |
| 2015 | Paul | Marshall | S14000024 | UK Independence Party | 601 |
| 2015 | Colin | Fox | S14000024 | Scottish Socialist Party | 197 |
| 2017 | Ian | MURRAY | S14000024 | Labour | 26269 |
| 2017 | Jim | EADIE | S14000024 | SNP | 10755 |
| 2017 | Stephanie Jane Harley | SMITH | S14000024 | Conservative | 9428 |
| 2017 | Alan Christopher | BEAL | S14000024 | Liberal Democrats | 1388 |
Warming up
Show the lastName, party and votes for the constituency 'S14000024' in 2017.
SELECTlastName,party,votes FROMge WHEREconstituency='E14000539'ANDyr=2017 ORDERBYvotesDESC
SELECTlastName,party,votes FROMge WHEREconstituency='S14000024'ANDyr=2017 ORDERBYvotesDESC
Who won?
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.
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
SELECTparty,votes, RANK()OVER(ORDERBYvotesDESC)asposn FROMge WHEREconstituency='E14000539'ANDyr=2017 ORDERBYvotes
SELECTparty,votes, RANK()OVER(ORDERBYvotesDESC)asposn FROMge WHEREconstituency='S14000024'ANDyr=2017 ORDERBYparty
PARTITION BY
The 2015 election is a different PARTITION to the 2017 election. We only care about the order of votes for each year.
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')
SELECTyr,party,votes, RANK()OVER(PARTITIONBYyrORDERBYvotesDESC)asposn FROMge WHEREconstituency='S14000021' ORDERBYparty,yr
SELECTyr,party,votes, RANK()OVER(PARTITIONBYyrORDERBYvotesDESC)asposn FROMge WHEREconstituency='S14000021' ORDERBYparty,yr
Edinburgh Constituency
Edinburgh constituencies are numbered S14000021 to S14000026.
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
SELECTconstituency,party,votes FROMge WHEREconstituencyBETWEEN'S14000021'AND'S14000026' ANDyr=2017 ORDERBYconstituency,votesDESC
SELECTconstituency,party,votes, RANK()OVER(PARTITIONBYconstituencyORDERBYvotesDESC) ASposn FROMge WHEREconstituencyBETWEEN'S14000021'AND'S14000026' ANDyr=2017 ORDERBYposn,constituency
Winners Only
You can use SELECT within SELECT to pick out only the winners in Edinburgh.
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
SELECTconstituency,party,votes FROMge WHEREconstituencyBETWEEN'S14000021'AND'S14000026' ANDyr=2017 ORDERBYconstituency,votesDESC
SELECTconstituency,party FROM( SELECTconstituency,party, RANK()OVER(PARTITIONBYconstituencyORDERBYvotesDESC) ASposn FROMge WHEREconstituencyBETWEEN'S14000021'AND'S14000026' ANDyr=2017 )ASed WHEREposn=1
Scottish seats
You can use COUNT and GROUP BY to see how each party did in Scotland. Scottish constituencies start with 'S'
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
SELECTconstituency,party,votes FROMge WHEREconstituencyBETWEEN'S14000021'AND'S14000026' ANDyr=2017 ORDERBYconstituency,votesDESC
SELECTparty,COUNT(1) FROM( SELECTconstituency,party, RANK()OVER(PARTITIONBYconstituencyORDERBYvotesDESC) ASposn FROMge WHEREconstituencyLIKE'S%' ANDyr=2017 )ASed WHEREposn=1 GROUPBYparty