———-USE OF Common Type Expressions ( CTE )

— FIND OUT 2nd LAST INSERTED ROW WITHOUT USING INDEX / IDENTITY COLUMN / TIME STAMP COLUMN ( PAWAN )

--Create a table
CREATE TABLE TestLastQuery
(

Name varchar(50),
Address varchar(70)

)

--Insert the values
INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Pawan'  ,  'Pune ')

INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Gauri'  ,  'A  Nagar ')

INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Saurabh'  ,  'A Nagar ')

INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Himanshu'  ,  'Padmavati ')

INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Rahul W'  ,  ' Jordan')

-- Use the below query to find out the last inserted row
WITH CTE
AS
(
SELECT TOP 2 Name , Address , row_number() over ( order by ( SELECT 2 ) )
AS ROWNUMBER FROM TestLastQuery ORDER BY  ROWNUMBER DESC
)
SELECT TOP 1 Name , Address from CTE ORDER BY ROWNUMBER
GO

____

Pawan Kumar

pawankkmr@hotmail.com