Getting Records with Min Max Value on Grouped in Single Table in postgresql

The table looks like this:

Existing Table:

table_1

Desired Result:

table_1

I have been able to get result like the following using this code:

table_1

But I would like to get minimum and maximum value on 'trans' row and their corresponding value on 'rate' row, "Grouped By date" on Single Table as following.

table_1

I've reduced a bit your sample data by entering only the first 10 rows.

create table tbl (id int, dt date, code text, trans text, rate int);

insert into tbl values
(1, '20181201', 'AAA', '2018120101', 399),
(2, '20181201', 'BBB', '2018120102', 244),
(3, '20181201', 'AAA', '2018120103', 400),
(4, '20181201', 'AAA', '2018120104', 397),
(5, '20181202', 'AAA', '2018120201', 395),
(6, '20181202', 'AAA', '2018120202', 405),
(7, '20181202', 'AAA', '2018120203', 399),
(8, '20181202', 'DDD', '2018120204', 355),
(9, '20181202', 'AAA', '2018120205', 402),
(10, '20181202', 'AAA', '2018120206', 403);

select
row_number() over (order by t0.code, t0.dt) as id,
t0.code,
t0.dt,
min_trans,
t1.rate as min_rate,
max_trans,
t2.rate as max_rate
from
(select code, dt, min(trans) min_trans, max(trans) max_trans
from tbl
group by code, dt) t0
join
tbl t1
on t1.code = t0.code
and t1.trans = t0.min_trans
join
tbl t2
on t2.code = t0.code
and t2.trans = t0.max_trans;

id | code | dt | min_trans | min_rate | max_trans | max_rate
-: | :--- | :--------- | :--------- | -------: | :--------- | -------:
1 | AAA | 2018-12-01 | 2018120101 | 399 | 2018120104 | 397
2 | AAA | 2018-12-02 | 2018120201 | 395 | 2018120206 | 403
3 | BBB | 2018-12-01 | 2018120102 | 244 | 2018120102 | 244
4 | DDD | 2018-12-02 | 2018120204 | 355 | 2018120204 | 355

db<>fiddle here

The first sub-query calculates MIN and MAX trans value grouped by code and date.

from
(select code, dt, min(trans) min_trans, max(trans) max_trans
from tbl
group by code, dt) t0

Then you can join twice your original table using code and max/min trans value fetched by the first sub-query.

join
tbl t1
on t1.code = t0.code
and t1.trans = t0.min_trans
join
tbl t2
on t2.code = t0.code
and t2.trans = t0.max_trans;

Adding a WHERE clause you can filter the final result by code.

StackExchange Database Administrators Q#224787, answer score: 2