2
 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
  • 1
    And what did you try already? Where do you have problems? Did you try each step separately first, and then to find a solution for all three together? – Patrick Mevzek Aug 16 '18 at 14:42
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.