CODEWITHSUNDEEP
mysqlsql
wingswingswings
wingswingswings

Reputation: 15

second to the last transaction from each vendor

I have transactions table, I need a list of only the second last transactions from each vendor, I'm using mysql5.7

Id customer_id order_id   transaction_date    status    vendor
1  BOB         TEST000001 2020-01-01          SHIPPED   Vendor BRO
2  JESS        TEST000002 2020-01-01          CANCELLED Vendor BRO
3  KAREN       TEST000003 2020-01-03          CANCELLED Vendor ALPHA
4  JESS        TEST000004 2020-02-04          CANCELLED Vendor CHARLIE

I have this so far, BUT INSTEAD it gives me literally the one row above of the last record (transaction) of each vendor.

SELECT * FROM transactions WHERE id IN( SELECT MAX(id)-1 FROM transactions GROUP BY vendor );

Upvotes: 0

Views: 965

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Use window functions:

SELECT t.*
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY vendor ORDER BY transaction_date DESC) as seqnum
      FROM transactions t
     ) t
WHERE seqnum = 1;

In older versions, you can use a correlated subquery:

select t.*
from transactions t
where t.transaction_date = (select t2.transaction_date
                            from transactions t2
                            where t2.vendor = t.vendor
                            order by t2.transaction_date desc
                            limit 1 offset 1
                           );

Upvotes: 2

Related Questions