Prioritization of data in SQL

Question

I need to join these 2 tables on Key. In the final table, I need Rank1 data in contact1 and if Rank1 is not there, I need to check next Rank. If Rank1 data comes to contact1, then I shouldn't consider Rank1 for contact2 and contact3. Is there a way to do in postgressql?

Table A:

Key1
1234
3456

Table B

Key2	Rank	contact
1234	1	owner
1234	2	co-owner
1234	3	CEO
3456	2	co-owner
3456	3	CEO

Final Table:

key	contact1	contact2	contact3
1234	owner	co-owner	CEO
3456	co-owner	CEO

Answer 1

You can achieve the desired result using a combination of the ROW_NUMBER() function and conditional aggregation. Here’s a SQL query that accomplishes your goal:

WITH RankedContacts AS (
    SELECT 
        Key2 AS [Key],
        Contact,
        ROW_NUMBER() OVER (PARTITION BY Key2 ORDER BY Rank) AS RowNum
    FROM 
        TableB
)
SELECT 
    [Key],
    MAX(CASE WHEN RowNum = 1 THEN Contact END) AS contact1,
    MAX(CASE WHEN RowNum = 2 THEN Contact END) AS contact2,
    MAX(CASE WHEN RowNum = 3 THEN Contact END) AS contact3
FROM 
    RankedContacts
GROUP BY 
    [Key];

This query first ranks the contacts based on their rank for each key. Then, it uses conditional aggregation to assign the appropriate contacts to contact1, contact2, and contact3.

A more extensive discussion of this issue can be found here: row number in sql server