Postgres 9.6

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

CREATE TABLE table0 (
    id text,
    type int,
    time text,
    site int
);

INSERT INTO table0 VALUES
('aa', 1, '12-18', 100),
('aa', 1, '12-10', 101),
('bb', 2, '12-10', 102),
('cc', 1, '12-09', 100),
('cc', 2, '12-12', 103),
('cc', 2, '12-01', 109),
('cc', 1, '12-07', 101),
('dd', 1, '12-08', 100);

SELECT * FROM table0;
8 rows affected



id
type
time
site


aa
1
12-18
100

aa
1
12-10
101

bb
2
12-10
102

cc
1
12-09
100

cc
2
12-12
103

cc
2
12-01
109

cc
1
12-07
101

dd
1
12-08
100


CREATE TABLE table1 (id) AS 
(VALUES ('aa'), ('cc'), ('cc'), ('dd'), ('dd'));

SELECT * FROM table1;
5 rows affected



id


aa

cc

cc

dd

dd


SELECT DISTINCT ON (t0.id)
    t0.id,
    type,
    time,
    first_value(site) OVER (PARTITION BY t0.id ORDER BY time) as site
FROM table0 t0
JOIN table1 t1 ON t0.id = t1.id
ORDER BY t0.id, type DESC, time


id
type
time
site


aa
1
12-10
101

cc
2
12-01
109

dd
1
12-08
100

id	type	time	site
aa	1	12-18	100
aa	1	12-10	101
bb	2	12-10	102
cc	1	12-09	100
cc	2	12-12	103
cc	2	12-01	109
cc	1	12-07	101
dd	1	12-08	100