0

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?

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by sknake
0

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

Edited by sknake: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.