Query by Position

[zautashvili]

Hi all,
I'm trying to write query in MSSQL which will return a single value, but the problem is, I want to get the result by a position in results, e.g.

SELECT [ID] FROM dbo.Users WHERE Status = 1

-- and then some code to get let's say fifth record from the result.

So far, what I've met is the TOP command that allows you to get top range of results.

So is it possible to retrieve a value by its position?

[sknake]

It is and conceptually it is identical to paging. In web apps you want to retrieve rows 0-9, 10-19, 20-29, etc. I normally hate to refer people to google for an answer but there are a lot of ways to go about doing this and you should select the one that best fits your needs. Search for " mssql paging "

Edit:
Well here is one example of how you could go about it:

IF OBJECT_ID('tempdb..#User', 'U') IS NOT NULL DROP TABLE #User
Create Table #User
(
  ID int identity(1000, 1) PRIMARY KEY,
  Name varchar(50),
  Active bit
)


Insert Into #User (Name, Active) Values ('a', 0)
Insert Into #User (Name, Active) Values ('b', 0)
Insert Into #User (Name, Active) Values ('c', 1)
Insert Into #User (Name, Active) Values ('d', 0)
Insert Into #User (Name, Active) Values ('e', 1)
Insert Into #User (Name, Active) Values ('f', 0)
Insert Into #User (Name, Active) Values ('g', 1)
Insert Into #User (Name, Active) Values ('h', 0)
Insert Into #User (Name, Active) Values ('i', 1)

Select *
From
(
  Select ROW_NUMBER() OVER (ORDER BY ID ASC) AS ROWID, * 
  FROM #User (NOLOCK)
) as tbl
Where RowId = 5