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&lt;>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.