Return only one row based on search-CodePudding

Query

select 
a.id,
a.ba,
b.status,
b.custid
from balist as a
inner join customer as b
on a.ba = b.ba

I have a table "balist" that has a list of (ba) and i inner join table "customer" on (ba) and right now by output is like the following

id	ba	status	custid
1	ba-1234455	A	123-321-123-321a
2	ba-1234455	I	123-321-123-321a
3	ba-1234457	A	123-321-123-321b
4	ba-1234458	A	123-321-123-321c
5	ba-1234459	I	123-321-123-321d

and I want to return all A and I status but remove the row that has status I that also have a A status. Like the following.

I have a table customer like the following

id	ba	status	custid
1	ba-1234455	A	123-321-123-321a
3	ba-1234457	A	123-321-123-321b
4	ba-1234458	A	123-321-123-321c
5	ba-1234459	I	123-321-123-321d

CodePudding user response：

You could use a row_number() to filter your resulting rows eg

SELECT
    id,ba,status,custid
FROM (
    SELECT 
        a.id,
        a.ba,
        b.status,
        b.custid,
        ROW_NUMBER() OVER (
             PARTITION BY a.ba
             ORDER BY b.status ASC
        ) as rn
    FROM
        balist as a
    INNER JOIN 
        customer as b ON a.ba = b.ba
)
WHERE rn=1

Let me know if this works for you.