How to narrow down records in SQL based on conditions

Question

 Customer   Rank      Joining_date       salary
    A         2        2017-10-12         500
    A         1        2017-10-10         800     
    A         1        2017-10-20         400
    B         2        2017-05-20         200
    B         2        2017-05-15         100
    c         3        2017-06-10         600
    c         4        2017-06-05         600

Logic: For a given customer

if the Rank is 1 ,then retain all those records with rank 1 and drop rest

if customer has records with different rank ,select the record based on latest rank (order by rank desc) and drop the other

if the customer has records with same rank (other than 1),then select the record based on the lowest salary (order by salary asc)**

Expected result

 Customer   Rank      Joining_date       salary
    A         1        2017-10-10         800     
    A         1        2017-10-20         400
    B         2        2017-05-15         100
    c         4        2017-06-05         600

Answer 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;

(SQL Fiddle)