Select Distinct with Inner Join Bringing duplicated Values

Question

I am having trouble using inner join to bring unique values from two tables.

SELECT distinct cast (opd.order_number as integer), p.payment_id ,
    pt.transaction_status, 
    opd.installments_number, 
    pt.amount, 
    pt.origin_amount,
    p.payment_capture_type,
    opd.brand,
    pt.created_date, 
    d."name" 
FROM payment p 
INNER JOIN payment_transaction pt ON pt.payment_id = p.payment_id
inner join "domain" d on d.domain_id = p.domain_id 
INNER JOIN attempted_online_payment_data opd ON p.payment_id = CAST ( opd.order_number AS INTEGER )
WHERE 
p.created_date >= '2021-11-01' and status = 1

This is the query I am using, and I would like to have a list of unique values of payment_id´s, but when I do, it brings two of the same payment_id for some reason.

How can I get only a unique row for each payment_id?

Answer 1

DISTINCT will remove duplicate result rows, that is rows where all columns are equal.

If you want only one result row per payment_id, there are two options:

• Use SELECT DISTINCT ON (payment_id) to return only the first row for each payment_id.

• Use GROUP BY payment_id and aggregate the results for each group.

What is the correct solution will depend on your requirements.