0

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.

enter image description here

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

0
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.