Key Bird Padilla
Key Bird Padilla

Reputation: 43

How can I select all 2nd to the last transaction

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

Answers (3)

cdaiga
cdaiga

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

Honey Dev P
Honey Dev P

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

Vash
Vash

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

Related Questions