Postgres 13

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

CREATE TABLE example_tbl (
  id "char"
, pid int
, sid int
, tid text
, other_columns int
);

INSERT INTO example_tbl (id, pid, sid, tid, other_columns)
VALUES 
 ('c', 157, 85, 'A32', 1),
 ('c', 157, 85, 'A32', 2),
 ('c', 157, 85, 'A32', 3),
 ('c', 157, 85, 'A32', 4),
 ('c', 157, 85, 'A32', 5),
 ('c', 157, 85, 'A32', 6),
 ('c', 157, 85, 'A32', 7),
 ('c', 157, 85, 'A32', 8),
 ('c', 157, 85, 'A32', 9),
 ('c', 157, 85, 'A32', 10),
 ('c', 157, 85, 'A32', 11),
 ('Q', 157, 81, 'A62', 1),
 ('Q', 157, 81, 'A62', 2),
 ('Q', 157, 81, 'A62', 3),
 ('Q', 157, 81, 'A62', 4),
 ('Q', 157, 81, 'A62', 5),
 ('Q', 157, 81, 'A62', 6),
 ('Q', 157, 81, 'A62', 7),
 ('Q', 157, 81, 'A62', 8),
 ('Q', 157, 81, 'A62', 9),
 ('Q', 157, 81, 'A62', 10),
 ('Q', 157, 81, 'A62', 11),
 ('f', 598, 51, 'A62', NULL),
 ('w', 598, 49, 'A32', 9),
 ('Z', 598, 44, 'A62', NULL),
 ('r', 598, 16, 'A32', 10),
 ('O', 118, 93, 'A32', 1),
 ('G', 118, 38, 'A32', 4),
 ('U', 118, 90, 'A32', 1),
 ('U', 118, 90, 'A32', 2),
 ('U', 118, 90, 'A32', 3),
 ('U', 118, 90, 'A32', 4),
 ('U', 118, 90, 'A32', 5),
 ('U', 118, 90, 'A32', 6),
 ('U', 118, 90, 'A32', 7),
 ('U', 118, 90, 'A32', 8),
 ('U', 118, 90, 'A32', 9),
 ('U', 118, 90, 'A32', 10),
 ('U', 118, 90, 'A32', 11),
 ('m', 118, 37, 'A62', 1),
 ('J', 118, 54, 'A32', 20),
 ('a', 118, 59, 'A32', 11),
 ('s', 118, 18, 'A62', 8),
 ('y', 118, 33, 'A62', NULL),
 ('N', 118, 79, 'A62', NULL),
 ('l', 118, 35, 'A32', 9),
 ('n', 118, 63, 'A32', 5),
 ('R', 118, 86, 'A62', 1),
 ('R', 118, 86, 'A62', 2),
 ('R', 118, 86, 'A62', 3),
 ('R', 118, 86, 'A62', 4),
 ('R', 118, 86, 'A62', 5),
 ('R', 118, 86, 'A62', 6),
 ('R', 118, 86, 'A62', 7),
 ('R', 118, 86, 'A62', 8),
 ('R', 118, 86, 'A62', 9),
 ('R', 118, 86, 'A62', 10),
 ('R', 118, 86, 'A62', 11),
 ('H', 118, 23, 'A32', 1),
 ('H', 118, 23, 'A32', 2),
 ('H', 118, 23, 'A32', 3),
 ('H', 118, 23, 'A32', 4),
 ('H', 118, 23, 'A32', 5),
 ('H', 118, 23, 'A32', 6),
 ('H', 118, 23, 'A32', 7),
 ('H', 118, 23, 'A32', 8),
 ('H', 118, 23, 'A32', 9),
 ('H', 118, 23, 'A32', 10),
 ('H', 118, 23, 'A32', 11),
 ('B', 118, 43, 'A62', 39),
 ('h', 118, 60, 'A62', NULL),
 ('p', 118, 72, 'A32', 1),
 ('v', 118, 22, 'A32', 5),
 ('I', 118, 89, 'A62', 9),
 ('T', 118, 17, 'A62', 1),
 ('F', 118, 41, 'A32', 10),
 ('z', 118, 55, 'A32', 6),
 ('Y', 118, 75, 'A32', NULL),
 ('u', 118, 48, 'A62', 9),
 ('x', 783, 27, 'A32', 10),
 ('V', 783, 11, 'A62', 8),
 ('i', 783, 61, 'A62', 1),
 ('i', 783, 61, 'A62', 2),
 ('i', 783, 61, 'A62', 3),
 ('i', 783, 61, 'A62', 4),
 ('i', 783, 61, 'A62', 5),
 ('i', 783, 61, 'A62', 6),
 ('i', 783, 61, 'A62', 7),
 ('i', 783, 61, 'A62', 8),
 ('i', 783, 61, 'A62', 9),
 ('i', 783, 61, 'A62', 10),
 ('i', 783, 61, 'A62', 11);

CREATE TABLE



INSERT 0 92


SELECT *, first_value(id) OVER (PARTITION BY tid
                                ORDER BY CASE pid
                                            WHEN 157 THEN 1
                                            WHEN 598 THEN 2
                                            WHEN 118 THEN 3
                                            WHEN 783 THEN 4
                                         END, sid
                                GROUPS BETWEEN 1 PRECEDING AND 1 PRECEDING
                                ) AS prev_id
FROM   example_tbl;


id
pid
sid
tid
other_columns
prev_id


c
157
85
A32
3
null

c
157
85
A32
5
null

c
157
85
A32
6
null

c
157
85
A32
7
null

c
157
85
A32
8
null

c
157
85
A32
9
null

c
157
85
A32
10
null

c
157
85
A32
11
null

c
157
85
A32
1
null

c
157
85
A32
2
null

c
157
85
A32
4
null

r
598
16
A32
10
c

w
598
49
A32
9
r

v
118
22
A32
5
w

H
118
23
A32
1
v

H
118
23
A32
10
v

H
118
23
A32
9
v

H
118
23
A32
8
v

H
118
23
A32
7
v

H
118
23
A32
6
v

H
118
23
A32
5
v

H
118
23
A32
4
v

H
118
23
A32
3
v

H
118
23
A32
2
v

H
118
23
A32
11
v

l
118
35
A32
9
H

G
118
38
A32
4
l

F
118
41
A32
10
G

J
118
54
A32
20
F

z
118
55
A32
6
J

a
118
59
A32
11
z

n
118
63
A32
5
a

p
118
72
A32
1
n

Y
118
75
A32
null
p

U
118
90
A32
7
Y

U
118
90
A32
8
Y

U
118
90
A32
9
Y

U
118
90
A32
10
Y

U
118
90
A32
11
Y

U
118
90
A32
6
Y

U
118
90
A32
5
Y

U
118
90
A32
4
Y

U
118
90
A32
3
Y

U
118
90
A32
2
Y

U
118
90
A32
1
Y

O
118
93
A32
1
U

x
783
27
A32
10
O

Q
157
81
A62
1
null

Q
157
81
A62
2
null

Q
157
81
A62
3
null

Q
157
81
A62
4
null

Q
157
81
A62
5
null

Q
157
81
A62
6
null

Q
157
81
A62
7
null

Q
157
81
A62
8
null

Q
157
81
A62
9
null

Q
157
81
A62
10
null

Q
157
81
A62
11
null

Z
598
44
A62
null
Q

f
598
51
A62
null
Z

T
118
17
A62
1
f

s
118
18
A62
8
T

y
118
33
A62
null
s

m
118
37
A62
1
y

B
118
43
A62
39
m

u
118
48
A62
9
B

h
118
60
A62
null
u

N
118
79
A62
null
h

R
118
86
A62
1
N

R
118
86
A62
11
N

R
118
86
A62
10
N

R
118
86
A62
9
N

R
118
86
A62
8
N

R
118
86
A62
7
N

R
118
86
A62
6
N

R
118
86
A62
5
N

R
118
86
A62
4
N

R
118
86
A62
3
N

R
118
86
A62
2
N

I
118
89
A62
9
R

V
783
11
A62
8
I

i
783
61
A62
1
V

i
783
61
A62
2
V

i
783
61
A62
3
V

i
783
61
A62
4
V

i
783
61
A62
5
V

i
783
61
A62
6
V

i
783
61
A62
7
V

i
783
61
A62
8
V

i
783
61
A62
9
V

i
783
61
A62
10
V

i
783
61
A62
11
V



SELECT 92


-- with explicitly ORDER BY (optional)
SELECT *
     , first_value(id) OVER (PARTITION BY tid
                             ORDER BY CASE pid
                                         WHEN 157 THEN 1
                                         WHEN 598 THEN 2
                                         WHEN 118 THEN 3
                                         WHEN 783 THEN 4
                                       END, sid
                             GROUPS BETWEEN 1 PRECEDING AND 1 PRECEDING
                             ) AS prev_id
FROM   example_tbl
ORDER BY tid, CASE pid
         WHEN 157 THEN 1
         WHEN 598 THEN 2
         WHEN 118 THEN 3
         WHEN 783 THEN 4
       END, sid, other_columns;


id
pid
sid
tid
other_columns
prev_id


c
157
85
A32
1
null

c
157
85
A32
2
null

c
157
85
A32
3
null

c
157
85
A32
4
null

c
157
85
A32
5
null

c
157
85
A32
6
null

c
157
85
A32
7
null

c
157
85
A32
8
null

c
157
85
A32
9
null

c
157
85
A32
10
null

c
157
85
A32
11
null

r
598
16
A32
10
c

w
598
49
A32
9
r

v
118
22
A32
5
w

H
118
23
A32
1
v

H
118
23
A32
2
v

H
118
23
A32
3
v

H
118
23
A32
4
v

H
118
23
A32
5
v

H
118
23
A32
6
v

H
118
23
A32
7
v

H
118
23
A32
8
v

H
118
23
A32
9
v

H
118
23
A32
10
v

H
118
23
A32
11
v

l
118
35
A32
9
H

G
118
38
A32
4
l

F
118
41
A32
10
G

J
118
54
A32
20
F

z
118
55
A32
6
J

a
118
59
A32
11
z

n
118
63
A32
5
a

p
118
72
A32
1
n

Y
118
75
A32
null
p

U
118
90
A32
1
Y

U
118
90
A32
2
Y

U
118
90
A32
3
Y

U
118
90
A32
4
Y

U
118
90
A32
5
Y

U
118
90
A32
6
Y

U
118
90
A32
7
Y

U
118
90
A32
8
Y

U
118
90
A32
9
Y

U
118
90
A32
10
Y

U
118
90
A32
11
Y

O
118
93
A32
1
U

x
783
27
A32
10
O

Q
157
81
A62
1
null

Q
157
81
A62
2
null

Q
157
81
A62
3
null

Q
157
81
A62
4
null

Q
157
81
A62
5
null

Q
157
81
A62
6
null

Q
157
81
A62
7
null

Q
157
81
A62
8
null

Q
157
81
A62
9
null

Q
157
81
A62
10
null

Q
157
81
A62
11
null

Z
598
44
A62
null
Q

f
598
51
A62
null
Z

T
118
17
A62
1
f

s
118
18
A62
8
T

y
118
33
A62
null
s

m
118
37
A62
1
y

B
118
43
A62
39
m

u
118
48
A62
9
B

h
118
60
A62
null
u

N
118
79
A62
null
h

R
118
86
A62
1
N

R
118
86
A62
2
N

R
118
86
A62
3
N

R
118
86
A62
4
N

R
118
86
A62
5
N

R
118
86
A62
6
N

R
118
86
A62
7
N

R
118
86
A62
8
N

R
118
86
A62
9
N

R
118
86
A62
10
N

R
118
86
A62
11
N

I
118
89
A62
9
R

V
783
11
A62
8
I

i
783
61
A62
1
V

i
783
61
A62
2
V

i
783
61
A62
3
V

i
783
61
A62
4
V

i
783
61
A62
5
V

i
783
61
A62
6
V

i
783
61
A62
7
V

i
783
61
A62
8
V

i
783
61
A62
9
V

i
783
61
A62
10
V

i
783
61
A62
11
V



SELECT 92


-- for  Postgres 9.5 

SELECT e.*, p.prev_id
FROM   example_tbl e
JOIN  (
   SELECT *
        , lag(id) OVER (PARTITION BY tid
                        ORDER BY CASE pid
                                    WHEN 157 THEN 1
                                    WHEN 598 THEN 2
                                    WHEN 118 THEN 3
                                    WHEN 783 THEN 4
                                 END, sid) AS prev_id
   FROM  (
      SELECT DISTINCT ON (tid, pid, sid)
             tid, pid, sid, id
      FROM   example_tbl
      ) dist
   ) p USING (tid, pid, sid)
ORDER BY tid, CASE pid             -- ORDER BY optional
                 WHEN 157 THEN 1
                 WHEN 598 THEN 2
                 WHEN 118 THEN 3
                 WHEN 783 THEN 4
              END, sid, other_columns;


id
pid
sid
tid
other_columns
prev_id


c
157
85
A32
1
null

c
157
85
A32
2
null

c
157
85
A32
3
null

c
157
85
A32
4
null

c
157
85
A32
5
null

c
157
85
A32
6
null

c
157
85
A32
7
null

c
157
85
A32
8
null

c
157
85
A32
9
null

c
157
85
A32
10
null

c
157
85
A32
11
null

r
598
16
A32
10
c

w
598
49
A32
9
r

v
118
22
A32
5
w

H
118
23
A32
1
v

H
118
23
A32
2
v

H
118
23
A32
3
v

H
118
23
A32
4
v

H
118
23
A32
5
v

H
118
23
A32
6
v

H
118
23
A32
7
v

H
118
23
A32
8
v

H
118
23
A32
9
v

H
118
23
A32
10
v

H
118
23
A32
11
v

l
118
35
A32
9
H

G
118
38
A32
4
l

F
118
41
A32
10
G

J
118
54
A32
20
F

z
118
55
A32
6
J

a
118
59
A32
11
z

n
118
63
A32
5
a

p
118
72
A32
1
n

Y
118
75
A32
null
p

U
118
90
A32
1
Y

U
118
90
A32
2
Y

U
118
90
A32
3
Y

U
118
90
A32
4
Y

U
118
90
A32
5
Y

U
118
90
A32
6
Y

U
118
90
A32
7
Y

U
118
90
A32
8
Y

U
118
90
A32
9
Y

U
118
90
A32
10
Y

U
118
90
A32
11
Y

O
118
93
A32
1
U

x
783
27
A32
10
O

Q
157
81
A62
1
null

Q
157
81
A62
2
null

Q
157
81
A62
3
null

Q
157
81
A62
4
null

Q
157
81
A62
5
null

Q
157
81
A62
6
null

Q
157
81
A62
7
null

Q
157
81
A62
8
null

Q
157
81
A62
9
null

Q
157
81
A62
10
null

Q
157
81
A62
11
null

Z
598
44
A62
null
Q

f
598
51
A62
null
Z

T
118
17
A62
1
f

s
118
18
A62
8
T

y
118
33
A62
null
s

m
118
37
A62
1
y

B
118
43
A62
39
m

u
118
48
A62
9
B

h
118
60
A62
null
u

N
118
79
A62
null
h

R
118
86
A62
1
N

R
118
86
A62
2
N

R
118
86
A62
3
N

R
118
86
A62
4
N

R
118
86
A62
5
N

R
118
86
A62
6
N

R
118
86
A62
7
N

R
118
86
A62
8
N

R
118
86
A62
9
N

R
118
86
A62
10
N

R
118
86
A62
11
N

I
118
89
A62
9
R

V
783
11
A62
8
I

i
783
61
A62
1
V

i
783
61
A62
2
V

i
783
61
A62
3
V

i
783
61
A62
4
V

i
783
61
A62
5
V

i
783
61
A62
6
V

i
783
61
A62
7
V

i
783
61
A62
8
V

i
783
61
A62
9
V

i
783
61
A62
10
V

i
783
61
A62
11
V



SELECT 92

id	pid	sid	tid	other_columns	prev_id
c	157	85	A32	3	null
c	157	85	A32	5	null
c	157	85	A32	6	null
c	157	85	A32	7	null
c	157	85	A32	8	null
c	157	85	A32	9	null
c	157	85	A32	10	null
c	157	85	A32	11	null
c	157	85	A32	1	null
c	157	85	A32	2	null
c	157	85	A32	4	null
r	598	16	A32	10	c
w	598	49	A32	9	r
v	118	22	A32	5	w
H	118	23	A32	1	v
H	118	23	A32	10	v
H	118	23	A32	9	v
H	118	23	A32	8	v
H	118	23	A32	7	v
H	118	23	A32	6	v
H	118	23	A32	5	v
H	118	23	A32	4	v
H	118	23	A32	3	v
H	118	23	A32	2	v
H	118	23	A32	11	v
l	118	35	A32	9	H
G	118	38	A32	4	l
F	118	41	A32	10	G
J	118	54	A32	20	F
z	118	55	A32	6	J
a	118	59	A32	11	z
n	118	63	A32	5	a
p	118	72	A32	1	n
Y	118	75	A32	null	p
U	118	90	A32	7	Y
U	118	90	A32	8	Y
U	118	90	A32	9	Y
U	118	90	A32	10	Y
U	118	90	A32	11	Y
U	118	90	A32	6	Y
U	118	90	A32	5	Y
U	118	90	A32	4	Y
U	118	90	A32	3	Y
U	118	90	A32	2	Y
U	118	90	A32	1	Y
O	118	93	A32	1	U
x	783	27	A32	10	O
Q	157	81	A62	1	null
Q	157	81	A62	2	null
Q	157	81	A62	3	null
Q	157	81	A62	4	null
Q	157	81	A62	5	null
Q	157	81	A62	6	null
Q	157	81	A62	7	null
Q	157	81	A62	8	null
Q	157	81	A62	9	null
Q	157	81	A62	10	null
Q	157	81	A62	11	null
Z	598	44	A62	null	Q
f	598	51	A62	null	Z
T	118	17	A62	1	f
s	118	18	A62	8	T
y	118	33	A62	null	s
m	118	37	A62	1	y
B	118	43	A62	39	m
u	118	48	A62	9	B
h	118	60	A62	null	u
N	118	79	A62	null	h
R	118	86	A62	1	N
R	118	86	A62	11	N
R	118	86	A62	10	N
R	118	86	A62	9	N
R	118	86	A62	8	N
R	118	86	A62	7	N
R	118	86	A62	6	N
R	118	86	A62	5	N
R	118	86	A62	4	N
R	118	86	A62	3	N
R	118	86	A62	2	N
I	118	89	A62	9	R
V	783	11	A62	8	I
i	783	61	A62	1	V
i	783	61	A62	2	V
i	783	61	A62	3	V
i	783	61	A62	4	V
i	783	61	A62	5	V
i	783	61	A62	6	V
i	783	61	A62	7	V
i	783	61	A62	8	V
i	783	61	A62	9	V
i	783	61	A62	10	V
i	783	61	A62	11	V