ROW_NUMBER get item at specific position

by Ivan Gerasimenko

JavaScript

DECLARE @position INT;
SET @position = 3

DECLARE @tb AS TABLE (
	Id INT PRIMARY KEY IDENTITY (1,1)
	,[Name] NVARCHAR(255)
	,[Date] DATETIME
)
INSERT INTO @tb ([Name], [Date]) VALUES ('Jim', DATEADD(dd, -ROUND(RAND()*100, 0), GETDATE()));
INSERT INTO @tb ([Name], [Date]) VALUES ('Kim', DATEADD(dd, -ROUND(RAND()*100, 0), GETDATE()));
INSERT INTO @tb ([Name], [Date]) VALUES ('Lin', DATEADD(dd, -ROUND(RAND()*100, 0), GETDATE()));
INSERT INTO @tb ([Name], [Date]) VALUES ('Ben', DATEADD(dd, -ROUND(RAND()*100, 0), GETDATE()));
INSERT INTO @tb ([Name], [Date]) VALUES ('Tim', DATEADD(dd, -ROUND(RAND()*100, 0), GETDATE()));
INSERT INTO @tb ([Name], [Date]) VALUES ('Tom', DATEADD(dd, -ROUND(RAND()*100, 0), GETDATE()));

SELECT 
	[counter] = ROW_NUMBER() OVER(ORDER BY [Date])
	,* 
FROM @tb


SELECT *
FROM
	(SELECT 
		[counter] = ROW_NUMBER() OVER(ORDER BY [Date])
		,* 
	FROM @tb) AS tb
WHERE [counter] = @position