Reputation: 43
Hi all how can I select all 2nd last transaction.
Transaction log
------------------------------
ID|MemberName|TransactionDate|
------------------------------
1 |Member 1 |1/1/2017 |
------------------------------
2 |Member 1 |1/2/2017 | <- I want to select this transaction
------------------------------
3 |Member 1 |1/3/2017 |This the last transaction of this client
------------------------------
4 |Member 2 |2/1/2017 |
------------------------------
5 |Member 2 |2/2/2017 |<- I want to select this transaction
------------------------------
6 |Member 2 |2/3/2017 |This the last transaction of this client
I want to select all 2nd to the last transaction of all of my clients.
Upvotes: 0
Views: 590
Reputation: 4937
I just made use on aggregation and join as shown in the query below:
SELECT A.* FROM TRANSACTION_LOG A INNER JOIN
(SELECT MEMBER_NAME, MAX(ID) ID FROM TRANSACTION_LOG
GROUP BY MEMBER_NAME) B
ON A.MEMBER_NAME=B.MEMBER_NAME AND A.ID=B.ID-1;
Here is an SQL Fiddle DEMO
Upvotes: 0
Reputation: 27
Select top(1) a.Id,a.MemberName, a.TransactionDate from (select top(2) Id,MemberName,TransactionDate from Transaction_log order by Id desc) as a order by a.Id asc;
Assumed table name to be Transaction_Log. I hope this worked for you.
Upvotes: 0
Reputation: 1787
Try ROW_NUMBER()
functionality(unless you are using MySQL).
SELECT A.ID, A.MEMBERNAME, A.TRANSACTIONDATE
FROM
(
SELECT ID, MEMBERNAME, TRANSACTIONDATE, ROW_NUMBER() OVER (PARTITION BY MEMBERNAME ORDER BY TRANSACTIONDATE DESC) AS RNUM
FROM TRANSACTION_TABLE
) A
WHERE A.RNUM = 2;
It creates indexing for all entries within each type of Membername and orders the numbering in descending order(meaning the first index represents the latest transactiondate). From this index column, you can give a filter of rnum = 2 for the second last transactiondate.
Upvotes: 2