2 use TABLE edited Aug 17 '18 at 9:21 CL. 3,08511 gold badge1212 silver badges1616 bronze badges The rank 1 customers and the other customers behave differently, so just use different queries. The last two logic conditions can be computed with a simple ORDER BY per customer; to relate that order back to the entire row requires a window function: ``````WITH Rank1Customers AS ( SELECT DISTINCT Customer FROM MyTable WHERE Rank = 1 ) SELECT Customer, Rank, Joining_date, Salary FROM MyTable WHERE Customer IN (SELECT Customer FROMTABLE Rank1Customers) AND Rank = 1 UNION ALL SELECT Customer, Rank, Joining_date, Salary FROM (SELECT *, row_number() OVER (PARTITION BY Customer ORDER BY Rank DESC, Salary ASC) AS OrderNr FROM MyTable WHERE Customer NOT IN (SELECT Customer FROMTABLE Rank1Customers) ) AS _ WHERE OrderNr = 1; `````` The rank 1 customers and the other customers behave differently, so just use different queries. The last two logic conditions can be computed with a simple ORDER BY per customer; to relate that order back to the entire row requires a window function: ``````WITH Rank1Customers AS ( SELECT DISTINCT Customer FROM MyTable WHERE Rank = 1 ) SELECT Customer, Rank, Joining_date, Salary FROM MyTable WHERE Customer IN (SELECT Customer FROM Rank1Customers) AND Rank = 1 UNION ALL SELECT Customer, Rank, Joining_date, Salary FROM (SELECT *, row_number() OVER (PARTITION BY Customer ORDER BY Rank DESC, Salary ASC) AS OrderNr FROM MyTable WHERE Customer NOT IN (SELECT Customer FROM Rank1Customers) ) AS _ WHERE OrderNr = 1; `````` The rank 1 customers and the other customers behave differently, so just use different queries. The last two logic conditions can be computed with a simple ORDER BY per customer; to relate that order back to the entire row requires a window function: ``````WITH Rank1Customers AS ( SELECT DISTINCT Customer FROM MyTable WHERE Rank = 1 ) SELECT Customer, Rank, Joining_date, Salary FROM MyTable WHERE Customer IN (TABLE Rank1Customers) AND Rank = 1 UNION ALL SELECT Customer, Rank, Joining_date, Salary FROM (SELECT *, row_number() OVER (PARTITION BY Customer ORDER BY Rank DESC, Salary ASC) AS OrderNr FROM MyTable WHERE Customer NOT IN (TABLE Rank1Customers) ) AS _ WHERE OrderNr = 1; `````` 1 answered Aug 17 '18 at 8:41 CL. 3,08511 gold badge1212 silver badges1616 bronze badges The rank 1 customers and the other customers behave differently, so just use different queries. The last two logic conditions can be computed with a simple ORDER BY per customer; to relate that order back to the entire row requires a window function: ``````WITH Rank1Customers AS ( SELECT DISTINCT Customer FROM MyTable WHERE Rank = 1 ) SELECT Customer, Rank, Joining_date, Salary FROM MyTable WHERE Customer IN (SELECT Customer FROM Rank1Customers) AND Rank = 1 UNION ALL SELECT Customer, Rank, Joining_date, Salary FROM (SELECT *, row_number() OVER (PARTITION BY Customer ORDER BY Rank DESC, Salary ASC) AS OrderNr FROM MyTable WHERE Customer NOT IN (SELECT Customer FROM Rank1Customers) ) AS _ WHERE OrderNr = 1; ``````