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
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.
SELECT lastName, party, votes
FROM ge
WHERE constituency = 'E14000539' AND yr = 2017
ORDER BY votes DESC
SELECT lastName, party, votes
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY votes DESC
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','');
SELECT party, votes,
RANK() OVER (ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'E14000539' AND yr = 2017
ORDER BY votes
SELECT party, votes,
RANK() OVER (ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party
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','')
SELECT yr,party, votes,
RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party,yr
SELECT yr,party, votes,
RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party,yr
Edinburgh Constituency
Edinburgh constituencies are numbered S14000021 to S14000026.
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
SELECT constituency,party, votes
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY constituency,votes DESC
SELECT constituency,party, votes,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC)
AS posn
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY posn,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','');
SELECT constituency,party, votes
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY constituency,votes DESC
SELECT constituency, party
FROM (
SELECT constituency,party,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC)
AS posn
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
) AS ed
WHERE posn = 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','');
SELECT constituency,party, votes
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY constituency,votes DESC
SELECT party,COUNT(1)
FROM (
SELECT constituency,party,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC)
AS posn
FROM ge
WHERE constituency LIKE 'S%'
AND yr = 2017
) AS ed
WHERE posn = 1
GROUP BY party