Selecting last 2 rows of each dense_rank

Question

Using MSSQL, I am trying to get some information from a journal where one event happens directly after another event.

So what I am effectively aiming for, is to get a row number partitioned by a TransactionID, and then I need the last 2 rows (last 2 row number) for EACH transactionID (Ordered by a TxnDate field). There could be any number of rows per TransactionID.

So I would get:

JnlId    TxnId    RowNum
5        10001    65
2        10001    66
10       10002    11
8        10002    12
5        10003    15
98       10003    16

Any ideas how I could achieve this as I am at a loss! The end game after this is to filter out the 'JnlId' field for a select few of IDs.

Bit of a back story. This customer thinks their staff is stealing, so I need to filter out when they are cancelling items directly before finishing off each transaction.

Answer 1

Try this, I added some extra rows to make dense rank more obvious:

Test data:

DECLARE @t table(JnlId int,TxnId int,RowNum int,  TxnDate date)
INSERT @t values
(5, 10001,65, '2015-01-01'),
(2, 10001,66, '2015-01-02'),
(2, 10001,66, '2015-01-03'),
(2, 10001,66, '2015-01-04'),
(2, 10001,67, '2015-01-04'),
(2, 10001,67, '2015-01-04'),
(10,10002,11, '2015-01-03'),
(8, 10002,12, '2015-01-04'),
(5, 10003,15, '2015-01-05'),
(98,10003,16, '2015-01-06')

Query:

;WITH CTE AS
(
  SELECT 
    DENSE_RANK() over(partition by txnID order by TxnDate desc) rn,
    JnlId,  TxnId,  RowNum, TxnDate
  FROM @t
)
SELECT JnlId,  TxnId,  RowNum, TxnDate FROM CTE
WHERE rn<=2

Result:

JnlId  TxnId  RowNum  TxnDate
2      10001  66      2015-01-04
2      10001  67      2015-01-04
2      10001  67      2015-01-04
2      10001  66      2015-01-03
8      10002  12      2015-01-04
10     10002  11      2015-01-03
98     10003  16      2015-01-06
5      10003  15      2015-01-05