Here is a slightly verbose and hopefully self explanatory set based approach.
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* To rebuild the table, first execute the line below by highlighting it + F5,
and then the rest of the code.
DROP TABLE dbo.TBL_SOURCE
*/
IF (SELECT OBJECT_ID('dbo.TBL_SOURCE')) IS NULL
BEGIN
/* As there is an implied order in the set, an order directive must be added.
Otherwise there is no way of controling the order of the elements within
the set. To achieve this, an identity column is added which provides a
sequence of natural numbers in the order of the inserts.
*/
CREATE TABLE dbo.TBL_SOURCE
(
ROW_KEY INT IDENTITY(1,1) NOT NULL
,Name NVARCHAR(50) NOT NULL
,increamentalvalue INT NOT NULL
,toy NVARCHAR(10) NOT NULL
);
/* Populate the table with the sample data */
INSERT INTO dbo.TBL_SOURCE
(
Name
,increamentalvalue
,toy
)
/* When using the VALUES construct, there is no need to repeate the
insert statement.
*/
VALUES
('ABC',23,'Bat') ,('ABC',70,'Ball'),('ABC',80,'XXX') ,('ABC',105,'Doll')
,('ABC',111,'Bat'),('ABC',120,'XXX'),('ABC',142,'XXX'),('ABC',145,'Doll')
,('ABC',111,'Bat'),('ABC',120,'XXX'),('ABC',142,'XXX'),('ABC',145,'XXX');
END
/* Seed for an inline Tally table to do "set" based loops
*/
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
/* Sequentially marking the beginning and the end of each Group
*/
,BASE_SET_CONSTRUCT AS
(
SELECT
SR.ROW_KEY
,SR.Name
,SR.increamentalvalue
,SR.toy
,ROW_NUMBER() OVER
(
PARTITION BY SR.toy
ORDER BY SR.ROW_KEY
) AS GROUP_KEY
FROM dbo.TBL_SOURCE SR
)
/* Count the number of groups in the BASE_SET_CONSTRUCT set
*/
,NUMBER_OF_GROUPS AS
(
SELECT
MAX(BSC.GROUP_KEY) AS NUM_GROUPS
FROM BASE_SET_CONSTRUCT BSC
WHERE BSC.toy = 'Bat'
)
/* Generate a number sequence with equal number of elements as the number
of groups in the BASE_SET_CONSTRUCT set
*/
,NUMS(N) AS (SELECT TOP(SELECT NUM_GROUPS FROM NUMBER_OF_GROUPS) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7
)
/* Using the NUMS sequence to retrieve the start and end value of
each group in the BASE_SET_CONSTRUCT set. The ROW_NUMBER function
assigns the value of 1 to the last entry in each group, exactly
what is asked for.
*/
,LAST_DOLL_OR_BAT AS
(
SELECT
NM.N
,ROW_NUMBER() OVER
(
PARTITION BY BSC.GROUP_KEY
ORDER BY BSC.ROW_KEY DESC
) AS BSC_RID
,BSC.ROW_KEY
,BSC.Name
,BSC.increamentalvalue
,BSC.toy
FROM NUMS NM
OUTER APPLY BASE_SET_CONSTRUCT BSC
WHERE BSC.GROUP_KEY = NM.N
AND BSC.toy IN ('Bat','Doll')
)
/* The next CTE will return the following set, what
remains is to get the desired increamentalvalue
of the previous toy.
N BSC_RID ROW_KEY Name increamentalvalue toy
-- -------- -------- ----- ----------------- -----
1 1 4 ABC 105 Doll
2 1 8 ABC 145 Doll
3 1 9 ABC 111 Bat
*/
,KEY_GROUP_MEMBERS AS
(
SELECT
LDOB.N
,LDOB.BSC_RID
,LDOB.ROW_KEY
,LDOB.Name
,LDOB.increamentalvalue
,LDOB.toy
FROM LAST_DOLL_OR_BAT LDOB
WHERE LDOB.BSC_RID = 1
)
/* Pulling everything together in a single query
*/
SELECT
KGM.Name
,KGM.increamentalvalue
,KGM.toy
,CASE
WHEN KGM.toy = 'Doll' THEN TS.Name
ELSE NULL
END AS PREV_NAME
,CASE
WHEN KGM.toy = 'Doll' THEN TS.increamentalvalue
ELSE NULL
END AS PREV_increamentalvalue
,CASE
WHEN KGM.toy = 'Doll' THEN TS.toy
ELSE NULL
END AS PREV_toy
FROM KEY_GROUP_MEMBERS KGM
INNER JOIN dbo.TBL_SOURCE TS
ON KGM.ROW_KEY = (TS.ROW_KEY + 1);
Results
Name increamentalvalue toy PREV_NAME PREV_increamentalvalue PREV_toy
----- ----------------- ----- ---------- ---------------------- ---------
ABC 105 Doll ABC 80 XXX
ABC 145 Doll ABC 142 XXX
ABC 111 Bat NULL NULL NULL
Edit: Typo