How can I select the difference between the highest and second highest field value using SQL?

Question

I have a table as follows:

id, party, constituency, votes

there are records for each of the UK voting constituencies, a data example is:

1, Conservative, Carlisle, 21780
2, Labour, Carlisle, 18932
3, Green, Carlisle, 2310
4, Conservative, Kensington, 22098
5, Labour, Kensington, 22056
6, Green, Kensington, 3209
7, Conservative, Islington, 32908
8, Labour, Islington, 33005
9, Green, Islington, 5409

I want to identify the seats where the lowest (and greatest) majorities exist. I would like to select all fields where the difference between the max vote and second placed vote, for a given constituency, is less than 50 for example. So I have tried:

SELECT *, 
       max.votes as max, 
       second_max.votes as max2 
FROM   election
WHERE  (max - max2) < 50  
ORDER BY votes DESC

Needless to say this does not work. Please can you help?

Answer 1

For databases where ROW_NUMBER doesn't exist, but EXISTS does?

An EXISTS can be used to get the top N within a partition.

And then you can aggregate those top 2 groups.

SELECT constituency
, MAX(votes) AS votes1
, MIN(votes) AS votes2
, MAX(votes)-MIN(votes) AS votes_diff
FROM election e
WHERE EXISTS 
(
   SELECT 1
   FROM election e2
   WHERE e2.constituency = e.constituency
     AND e2.votes > e.votes
   HAVING COUNT(*) < 2
)
GROUP BY constituency
HAVING (MAX(votes) - MIN(votes)) < 50
ORDER BY votes_diff DESC;

Using this sample data:

CREATE TABLE election (
 party varchar(30) not null, 
 constituency varchar(30) not null, 
 votes numeric(10) not null
);
INSERT INTO election (party, constituency, votes) VALUES
('Conservative', 'Carlisle', 21780),
('Labour', 'Carlisle', 18932),
('Green', 'Carlisle', 2310),
('Conservative', 'Kensington', 22098),
('Labour', 'Kensington', 22056),
('Green', 'Kensington', 3209),
('Conservative', 'Islington', 32908),
('Labour', 'Islington', 33005),
('Green', 'Islington', 5409);

Returns:

constituency    votes1  votes2  votes_diff
Kensington      22098   22056   42