Jalal Jalal - 2 months ago 6
SQL Question

SQL query to retrieve unique record based on role and email fields

I was trying to retrieve some records based on my requirement. As per the current table, a store can have any number of managers and/or cashiers with their respective email addresses. Now, I am trying to retrieve a new set of records which will have only one manager and/or one cashier per branch with their respective email address (there are no criteria to pick up specific manager cashier)

Current table:

**BranchName** **Role** **Email**

CA Cashier Cashier1@gmail.com

CA Cashier Cashier2@gmail.com

NY Manager Manager1@gmail.com

NY Manager Manager2@gmail.com

MASS Manager Manager@gmail.com

MASS Manager Massm@gmail.com


expected output

**BranchName** **Role** **Email**

CA Cashier Cashier1@gmail.com

NY Manager Manager1@gmail.com

MASS Manager massm@gmail.com


The output is based on the requirement that, branch to Role is one to one mapping whereas Role to branch is one- many ( role and email forms a uniqueness for that particular branch).

I have tried using the below query but I am able to see the duplicate roles for a single branch.

select BranchName,Email,Role
FROM (select BranchName,Email,Role ,
ROW_NUMBER() OVER (PARTITION BY Role ORDER BY Email) AS "C"
from Store
where Role IN ('Manager','General manager','Cashier'))
where C=1;


In my above query, while partitioning by role it only display single record with single branch instead of single role for all bracnhes. It could be because of the condition C=1, if I remove this, I am getting all the records.

I hope, I have provided enough details. Kindly provide some inputs to fix this issue.

Thanks !

Answer

If you don't have a preference/rule for choosing an email address given a branch name and role, then you just do a simple GROUP BY to get a result:

SELECT BranchName,
       Role,
       MIN(Email) AS Email
FROM Store
GROUP BY BranchName,
         Role
Comments