CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Remo
Remo

Reputation: 51

Last 5 rows in SQL with conditional

I have a table with the purchase information. It contains all the purchase information of a Pet Shop, since 2010. I bring the last 5 rows this way, and then I save only the last purchase in another table: 

SELECT *
FROM (
    SELECT 
        client_name,
        purchase_date,
        item,
        payment,
        ROW_NUMBER() OVER(PARTITION BY client_name ORDER BY purchase_date desc) rn
    FROM Pet_Shop
   ) x
WHERE rn <= 5
ORDER BY client_name

Which brings me 

client_name | purchase_date | item | payment
_____________________________________________
John        | 2019-09-14    | food | cash
John        | 2019-09-13    | ball | cash  
John        | 2019-09-12    | shampoo| cash
John        | 2019-09-11    | cookie| cash 
John        | 2019-09-11    | food  | cash
Mary        | 2019-09-14    | collar| cash
Mary        | 2019-07-14    | food  | cash
Mary        | 2019-06-14    | toy   | credit card
Mary        | 2019-06-14    | hamster | cash
Mary        | 2019-05-14    | food  | cash 
Austin      | 2019-09-18    | food  | cash
Austin      | 2019-09-11    | collar| cash
Austin      | 2019-09-10    | toy   | cash
Austin      | 2019-09-09    | catnip| cash
Austin      | 2019-09-11    | food  | cash

However, I have difficulty deleting customers who bought with a credit card. That is, if at any time customer in the last five purchases paid by credit card, it should not be listed. I tried something like WHERE payment like not '%card%', however, this meant that only the card registration did not appear and the following ones did.

Upvotes: 0

Views: 61

Answers (1)

forpas
forpas

Reputation: 164099

Wrap the subquery inside a CTE and use NOT EXISTS:

WITH cte AS (
    SELECT 
        client_name,
        purchase_date,
        item,
        payment,
        ROW_NUMBER() OVER(PARTITION BY client_name ORDER BY purchase_date desc) rn
    FROM Pet_Shop
)
SELECT c.*
FROM cte c
WHERE c.rn <= 5
AND NOT EXISTS (
  SELECT 1 FROM cte
  WHERE client_name = c.client_name AND payment LIKE '%card%'
)
ORDER BY c.client_name

Upvotes: 1

Related Questions