Group by based on priority in Oracle

Question

I need to write a stored procedure or SQL statements which needs to be run for around 1M records for an Oracle database. This should fetch the data based on priority as defined.

1 is highest priority and 4 is lowest.

When the records are group by using Cust ID, it should select only that record which is of highest priority as per source name else move to next source for priority within that group.

I am using old school ways by using LISTAGG and then check for value of Source name = A and then fetch all the data.

Is there any better way to extract the information.

The output should only contain records as per priority for each Group BY of Cust ID

Answer 1

You can use the analytical function ROW_NUMBER as follows:

SELECT * FROM
(SELECT T.*, ROW_NUMBER() OVER (PARTITION BY T.CUSTID ORDER BY P.PRIORITY) AS RN 
FROM YOUR_TABLE T JOIN PRIORITY_TABLE P
ON T.SOURCE_NAME = P.SOURCE_NAME)
WHERE RN = 1