PostgreSQL Question posted in
The official documentation can be found here.

How to get non repeated rows in postgresql

I have query like

select billing_number, created, amount_total_sum, days, country, status, check_in_date, concat(elements->> 'isPossible', elements-> 'UTCDateFrom' ->> 'date', ' - ', elements-> 'UTCDateTo' ->> 'date' )
from (select o.billing_number, o.created, o.amount_total_sum, t.check_in_date, t.check_out_date - t.check_in_date as days, t.hotel_country as country, s.title as status, json_array_elements(r.cancelation_rules) as elements
from tol.order o
join tol.service_cart sc on o.id = sc.order_id
join tol.ticket_hotel t on t.id = sc.ticket_uid
join tol.ticket_hotel_room r on r.ticket_uid = t.id
join tol.status s on s.id = o.status_id

The field r.cancelation_rules is a json array. Query works fine, but it return me duplicated rows, f.e.:

112 2023-02-18 16:08:25.000 4204.0  1   EN  Done    2023-02-18  true2023-02-18 00:00:00 - 2023-02-18 14:00:00
654 2023-02-18 16:09:31.000 3127.0  1   EN  Done    2023-03-06  true2022-09-05 00:00:00 - 2023-03-05 13:00:00
654 2023-02-18 16:09:31.000 3127.0  1   EN  Done    2023-03-06  true2023-03-05 13:00:00 - 2023-03-06 14:00:00
156 2023-02-18 16:11:23.000 5496.0  2   FR  Done    2023-06-19  true2023-02-18 00:00:00 - 2023-06-19 00:00:00
168 2023-02-18 16:13:48.000 15698.0 2   FR  Done    2023-02-23  true2022-08-22 00:00:00 - 2023-02-22 10:00:00

I wanna get distinct rows with only one row with each order but with all cancellation_rules, f.e.:

654 2023-02-18 16:09:31.000 3127.0  1   EN  Done    2023-03-06 true2022-09-05 00:00:00 - 2023-03-05 13:00:00 true2023-03-05 13:00:00 - 2023-03-06 14:00:00

Just cant get how to do it rn 🙁

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Its done! Just need use function string_agg like

    select billing_number, created, amount_total_sum, days, country, status, check_in_date, string_agg(concat(elements->> 'isPossible', elements-> 'UTCDateFrom' ->> 'date', ' - ', elements-> 'UTCDateTo' ->> 'date' ), ',')
from (select o.billing_number, o.created, o.amount_total_sum, t.check_in_date, t.check_out_date - t.check_in_date as days, t.hotel_country as country, s.title as status, json_array_elements(r.cancelation_rules) as elements
from tol.order o
join tol.service_cart sc on o.id = sc.order_id
join tol.ticket_hotel t on t.id = sc.ticket_uid
join tol.ticket_hotel_room r on r.ticket_uid = t.id
join tol.status s on s.id = o.status_id
group by 1,2,3,4,5,6,7

  2. 0

    Use DISTINCT statement on the "order" column to get unique order numbers.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search