Member Avatar for 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?

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)
  • 2 Contributors
  • 1 Reply
  • 149 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by sknake
Member Avatar for 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
Edited by sknake because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.