CODEWITHSUNDEEP
mysql
CH99
CH99

Reputation: 171

How to display only the second purchase made per account

I have a transactions table which has shows various transactions made by several accounts. Some make only one, others more than that. At the moment the SQL I have prints out the first purchase of each account but i need it to print out the second made by each account

SELECT account_id
     , purchase_date as second_purchase
     , amount as second_purchase_amount
FROM Transactions t
WHERE purchase_date NOT IN (SELECT MIN(purchase_date)
                            FROM Transactions m
                            )
GROUP BY account_id
HAVING purchase_date = MIN(purchase_date);

What needs to change that the second purchase date and amount are chosen? I tried adding in a count for the account_id but it was giving me the wrong value.

Upvotes: 0

Views: 479

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use variables to assign row numbers and get the 2nd purchase.

SELECT account_id,purchase_Date,amount 
FROM (
     SELECT account_id
         ,purchase_date
         ,amount
         --, @rn:=IF(account_id=@a_id and @pdate <> purchase_date,@rn+1,1) as rnum
         ,case when account_id=@a_id and @pdate <> purchase_date then @rn:=@rn+1
               when account_id=@a_id and @pdate=purchase_date then @rn:=@rn
          else @rn:=1 end as rnum
         , @pdate:=purchase_date
         , @a_id:=account_id
     FROM Transactions t
     CROSS JOIN (SELECT @rn:=0,@a_id:=-1,@pdate:='') r
     ORDER BY account_id, purchase_date
    ) x 
WHERE rnum=2

Explanation of how it works:

  • @rn:=0,@a_id:=-1,@pdate:='' - Declare 3 variables and initialize them, @rn for assigning the row numbers, @a_id to hold the account_id and @pdate to hold the purchase_date.

  • For the first row (ordered by account_id and purchase_date), account_id and @a_id, @pdate and purchase_date will be compared. As they wouldn't be equal, the when conditions fail and the else part would assign @rn=1. Also, the variable assignment happens after this. @aid and @pdate would be updated to current row's values. For the second row, if they are the same account and on a different date the first when condition will be executed and the @rn will be incremented by 1. If there are ties the second when condition would be executed and the @rn remains the same. You can run the inner query to check how the variables are assigned.

Upvotes: 1

McNets
McNets

Reputation: 10807

Number the rows and choose RowNumber = 2

select *
from (
    select
         @rn := case when @account_id = account_id then @rn + 1 else @rn := 1 end as RowNumber,
         @account_id := account_id as account_id, 
         purchase_date
    from 
        (select @rn := 1) x,
        (select @acount_id :=account_id as account_id, purchase_date 
         from Transactions 
         order by account_id, purchase_date)  y
     ) z
where RowNumber = 2;

Upvotes: 0

Related Questions