Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE UKRESULTS
(UKNUM      NUMERIC(4)    NOT NULL,
 PARTY      VARCHAR2(20)  NOT NULL,
 UKVOTES    NUMERIC(6)    NOT NULL,
PRIMARY KEY (UKNUM, PARTY)
);
INSERT INTO UKRESULTS
select 1, 'con', 6605 from dual
union all select 1, 'lab', 23745 from dual
union all select 1, 'lib', 8206 from dual
union all select 1, 'plaid', 1859 from dual
union all select 2, 'con', 7426 from dual
union all select 2, 'lab', 19262 from dual
union all select 2, 'sdp', 10118 from dual
union all select 2, 'snp', 3790 from dual
union all select 2, 'eco', 367 from dual
union all select 3, 'con', 15393 from dual
union all select 3, 'lab', 11812 from dual
union all select 3, 'sdp', 10372 from dual
union all select 3, 'snp', 1974 from dual
union all select 4, 'con', 31288 from dual
union all select 4, 'lab', 6070 from dual
union all select 4, 'lib', 19070 from dual
union all select 5, 'con', 24148 from dual
union all select 5, 'lab', 11864 from dual
union all select 5, 'sdp', 11599 from dual;
19 rows affected

select * from ukresults
order by uknum, ukvotes desc;


UKNUM
PARTY
UKVOTES


1
lab
23745

1
lib
8206

1
con
6605

1
plaid
1859

2
lab
19262

2
sdp
10118

2
con
7426

2
snp
3790

2
eco
367

3
con
15393

3
lab
11812

3
sdp
10372

3
snp
1974

4
con
31288

4
lib
19070

4
lab
6070

5
con
24148

5
lab
11864

5
sdp
11599


SELECT
  *
FROM
  (
    SELECT
      uknum,
      rank() OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS place,
      lag(ukvotes) OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS lag
    FROM
      ukresults
  )
WHERE
  place = 2;


UKNUM
PLACE
LAG


1
2
23745

2
2
null

3
2
null

4
2
null

5
2
null


SELECT
  *
FROM
  (
    SELECT
      uknum,
      rank() OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS place,
      lag(ukvotes) OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS lag,
      lead(ukvotes) OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS lead
    FROM
      ukresults
  )
WHERE
  place = 2;


UKNUM
PLACE
LAG
LEAD


1
2
23745
6605

2
2
19262
7426

3
2
15393
10372

4
2
31288
6070

5
2
24148
11599

UKNUM	PARTY	UKVOTES
1	lab	23745
1	lib	8206
1	con	6605
1	plaid	1859
2	lab	19262
2	sdp	10118
2	con	7426
2	snp	3790
2	eco	367
3	con	15393
3	lab	11812
3	sdp	10372
3	snp	1974
4	con	31288
4	lib	19070
4	lab	6070
5	con	24148
5	lab	11864
5	sdp	11599

UKNUM	PLACE	LAG
1	2	23745
2	2	null
3	2	null
4	2	null
5	2	null