2 use TABLE
source | link

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;

(SQL FiddleSQL Fiddle)

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;

(SQL Fiddle)

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;

(SQL Fiddle)

1
source | link

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;

(SQL Fiddle)