oracle sql getting duplicate invoice number according to status-CodePudding

I have below sql query with different invoices having different status.

WITH D AS (SELECT  'ABC' INV#,'PROCESSED' STATUS FROM DUAL UNION ALL
        SELECT  'ABC' INV#,'ERROR' STATUS FROM DUAL UNION ALL
        SELECT  'XYZ' INV#,'PROCESSED' STATUS FROM DUAL UNION ALL
        SELECT  'AJSKS' INV#,'ERROR' STATUS FROM DUAL union all
        SELECT  'SPG' INV#,'PROCESSED' STATUS FROM DUAL UNION ALL
        SELECT  'SPG' INV#,'ERROR' STATUS FROM DUAL
        )
        SELECT * FROM D

In case of duplicate Invoice# want to get invoice with status PROCESSED otherwise whatever the status is can return.

CodePudding user response：

You can use:

SELECT inv#, status
FROM   (
  SELECT d.*,
         COUNT(*) OVER (PARTITION BY inv#) AS cnt,
         ROW_NUMBER() OVER (
           PARTITION BY inv#
           ORDER BY CASE status WHEN 'PROCESSED' THEN 1 ELSE 2 END
         ) AS rn
  FROM   d
)
WHERE  cnt > 1
AND    rn  = 1;

Which, for your sample data, outputs:

INV# STATUS

ABC PROCESSED

SPG PROCESSED

INV#	STATUS
ABC	PROCESSED
SPG	PROCESSED

db<>fiddle here

CodePudding user response：

select * from (
   select * , row_number() over (partition by INV#, order by case STATUS when 'PROCESSED' then 1 else 0 end desc) rn 
   from D
) t where rn = 1