RowNum with SQLServer

Hi,
How can I get the 50 to 100 records of a select query (something like "rownum > 50 and rownum < 100" in Oracle)?

Thanks
eyaltilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustavo Perez BuenrostroCommented:
In SQL server there is no ROWNUM pseudocolumn as in Oracle. However, you can use any of these alternatives:

- ROWCOUNT option of SET statement.
- TOP argument of SELECT statement.
- IDENTITY function.

Taking into account your sample query you must use IDENTITY function. For more detail, see "IDENTITY (Function)(T-SQL)" topic in BOL.

Consider next code and let me know if it solves your problem:

if exists
  (select *
     from tempdb..sysobjects
    where id
         =object_id(N'tempdb..#table_name'))
drop table #table_name

select rownum=IDENTITY(int,1,1)
      ,col1
      ,col2
 into #table_name
 from table_name
 order by
       col1

select *
  from #table_name
 where rownum>50 and
       rownum<100

Note you are responsible for specifying the sort for the result set. ORDER BY clause affects the rownum column of each row.

PD: If you need a better advice post your table's structure.
0
eyaltilAuthor Commented:
I'm working IIS 4, and the problem is that I don't want to get the whole resoult set but 50 rows each time.

Note that the where clause of the select query is dynamic , meens that I can't use a fixed identity column.
I can run a stored procedure with the where clause that will update my identity column but this will not help because their are many users and each one of them can make a defrent where caluse.
0
Gustavo Perez BuenrostroCommented:
Why don't you post your table's structure?
Let me help you.
PD:A sample data would be useful.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Gustavo Perez BuenrostroCommented:
You can create a stored procedure that retrieves n rows from your table taking into account a specific column value. Check the code and let me know your opinion.

create table test_table (col_id int,col_data char(1))

declare @liId int
select @liId=1
while @liId<201
  begin
    insert test_table
    select @liId,(select char(cast(rand()*25 as int)+65))
    select @liId=@liId+1
  end

create proc spRetrieveData
 @liRowsReturned int=1 output -- specifies how many rows are returned
,@liLastRowId int=0 output -- returns last row id of the record set
as
begin
  select @liLastRowId=isnull(@liLastRowId,0)
        ,@liRowsReturned=isnull(@liRowsReturned,1)
  set rowcount @liRowsReturned
  select *
    from test_table
   where col_id>@liLastRowId

  select @liLastRowId=col_id
    from test_table
   where col_id>@liLastRowId

  select @liRowsReturned=@@rowcount
  set rowcount 0
end


declare
 @liRowsReturned int
,@liLastRowId int

select
 @liRowsReturned=50
,@liLastRowId=49

exec spRetrieveData
     @liRowsReturned output
    ,@liLastRowId output

select
 @liRowsReturned
,@liLastRowId
0
eyaltilAuthor Commented:
This whole code is to havy.
I can't run a stored procedure before any select taht a user does.
0
chigrikCommented:
Change string (5) from my example as you like.

CREATE PROCEDURE sp_fetch (@begin_id int, @end_id int) AS
declare @row_count int

set nocount on
select * into #tb_temp from pubs.dbo.authors --(5)
declare fetch_cursor cursor  for select * from #tb_temp
set @row_count = 1

open fetch_cursor
FETCH NEXT FROM fetch_cursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
     if (@row_count < @begin_id) or (@row_count > @end_id)
        delete from #tb_temp WHERE CURRENT OF fetch_cursor
     set @row_count = @row_count + 1
     FETCH NEXT FROM fetch_cursor
END

select * from #tb_temp
close fetch_cursor
deallocate fetch_cursor


If you want to choose rows from 5 to 10 from table authors database pubs you may run the following:
exec sp_fetch 5, 10
0
eyaltilAuthor Commented:
Adjusted points to 300
0
Gustavo Perez BuenrostroCommented:
Please, be more specific.
0
chigrikCommented:
CREATE PROCEDURE sp_fetch (@text_query varchar (1000), @begin_id int, @end_id int) AS
declare @execstr varchar (1100)
declare @row_count int
declare @exmplestr varchar (200)

set nocount on
set @exmplestr = REPLACE(CONVERT(varchar(80), newid()), '-', '_')
set @execstr = STUFF(@text_query, PATINDEX('%from%', @text_query) , 0, ' into tempdb..a' + @exmplestr + ' ')
exec (@execstr)

set @execstr = 'declare fetch_cursor cursor  for select * from tempdb..a' + @exmplestr
exec (@execstr)
set @row_count = 1

open fetch_cursor
FETCH NEXT FROM fetch_cursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
     if (@row_count <@begin_id) or (@row_count > @end_id)
        BEGIN
            set @execstr = 'delete from tempdb..a' + @exmplestr + ' WHERE CURRENT OF fetch_cursor '
            exec (@execstr)
        END
     set @row_count = @row_count + 1
     FETCH NEXT FROM fetch_cursor
END

set @execstr = 'select * from tempdb..a' + @exmplestr
exec (@execstr)
close fetch_cursor
deallocate fetch_cursor


It's my test example:

exec sp_fetch
'use pubs
SELECT t1.au_lname
FROM authors t1 INNER JOIN titleauthor t2
ON t1.au_id = t2.au_id', 5, 10

You can transfer any queries as first parameter, second parameter - first record for selection, third parameter - last record for selection.

I know, that it will work slowly and SQL Server is not intended for such navigation, but...
By the way, read about "Does SQL Server support row numbers like Oracle does?":
http://www.ntfaq.com/ntfaq/sql183.html#sql183

P.S. It's example for MS SQL Server 7.0.
0
eyaltilAuthor Commented:
This solution is really very bad for me because it may be very havy to run this stored procedure instead of any select query I need to do.

This application is going to run on a web page with IIS. I can't run a stored procedure for evry select on the screen.
0
mmipsCommented:
If you are using IIS 4.0 then why not use server side scripts to obtain a recordset and display only the number of records that you want. Then if the use updates you can display more records continuing from where you left off. I use this sucessfully with MSSQL7 and IIS 4.0 for web pages that may have to display thousands of records. Using this approach puts the load on the IIS server rather than the client .
0
eyaltilAuthor Commented:
mmips, can u please be more specific?
How can I open a recordset in the IIS side and not close it when the page is being send ti the client and then use it again when the client want more records?
0
hdrikCommented:
I assume that you are working with IIS 4.0, you probably code with ASP

in ASP, we can display record 1-20 in first page, and 21-40 in second page, and so on.

it goes like this:

<%
myConn = "DSN=myDSN;UID=SA;PWD=;"

myPage= Request("WhichPage")
if myPage="" then
   myPage = 1
End if
mypagesize=request("pagesize")
If  mypagesize="" then
   mypagesize=10
end if
mySQL=request("SQLquery")
IF  mySQL="" THEN
   mySQL=SQLtemp
END IF

set rstemp=Server.CreateObject("ADODB.Recordset")
rstemp.cursorlocation=aduseclient
rstemp.cachesize=5
rstemp.open mySQL,connectme
rstemp.movefirst
rstemp.pagesize=mypagesize
maxpages=cint(rstemp.pagecount)
maxrecs=cint(rstemp.pagesize)
rstemp.absolutepage=mypage
howmanyrecs=0
howmanyfields=rstemp.fields.count -1
response.write "Page " & mypage & " of " & maxpages & "<br>"
response.write "<table border='1'><tr>"

'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
   response.write "<td><b>" & rstemp(i).name & "</b></td>"
NEXT
response.write "</tr>"

' Now loop through the data
DO  UNTIL rstemp.eof OR howmanyrecs>=maxrecs
   response.write "<tr>"
   FOR i = 0 to howmanyfields
      fieldvalue=rstemp(i)
      If isnull(fieldvalue) THEN
         fieldvalue="n/a"
      END IF
      If trim(fieldvalue)="" THEN
         fieldvalue="&nbsp;"
      END IF
      response.write "<td valign='top'>"
      response.write fieldvalue
      response.write "</td>"
   next
   response.write "</tr>"
   rstemp.movenext
   howmanyrecs=howmanyrecs+1
LOOP
response.write "</table><p>"

' close, destroy
rstemp.close
set rstemp=nothing

' Now make the page _ of _ hyperlinks
Call PageNavBar

sub PageNavBar()
      pad=""
      scriptname=request.servervariables("script_name")
      response.write "<table rows='1' cols='1' width='97%'><tr>"
      response.write "<td>"
      response.write "<font size='2' color='black' face='Verdana, Arial,Helvetica, sans-serif'>"
      if (mypage mod 10) = 0 then
       counterstart = mypage - 9
      else
       counterstart = mypage - (mypage mod 10) + 1
      end if
      counterend = counterstart + 9
      if counterend > maxpages then counterend = maxpages
      if counterstart <> 1 then
       ref="<a href='" & scriptname
       ref=ref & "?whichpage=" & 1
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>First</a>&nbsp;:&nbsp;"
       Response.Write ref


       ref="<a href='" & scriptname
       ref=ref & "?whichpage=" & (counterstart - 1)
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>Previous</a>&nbsp;"
       Response.Write ref
      end if
      Response.Write "["
      for counter=counterstart to counterend
       If counter>=10 then
       pad=""
       end if
       if cstr(counter) <> mypage then
       ref="<a href='" & scriptname
       ref=ref & "?whichpage=" & counter
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>" & pad & counter & "</a>"
       else
       ref="<b>" & pad & counter & "</b>"
       end if
       response.write ref
       if counter <> counterend then response.write " "
      next
      Response.Write "]"
      if counterend <> maxpages then
       ref="&nbsp;<a href='" & scriptname
       ref=ref & "?whichpage=" & (counterend + 1)
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>Next</a>"
       Response.Write ref


       ref="&nbsp;:&nbsp;<a href='" & scriptname
       ref=ref & "?whichpage=" & maxpages
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>Last</a>"
       Response.Write ref
      end if
      response.write "<br></font>"
      response.write "</td>"
      response.write "</table>"
end sub
%>



I hope it help, please let me know information above code




0
nikhilhCommented:
I am also interested in the output of the discussion
0
mmipsCommented:
If you are using Interdev the DTC for recordset allows setting the number of records per page. If not then The server side type of script is a function that returns the number of records you displayed the first page second call passes that number back reopens a recordset and starts from there as an absolute position.This provides a decent way of displaying large numbers of records. I prefer the data grid DTC.
0
muralisCommented:
In order to implement a rownum feature the following solutions would help out.

1.If your table already consists an identity column you may use it equivalent to rownum.

2.The following method would help you out in any situation.

Create a temporary table with an identity column and based on that you retrieve the rows. This would be helpful even if a table does'nt contains an identity column.
0
eyaltilAuthor Commented:
hdrink, please answer this question, so I would be able to give you the points
0
hdrikCommented:
why you rejected my answer ?


I assume that you are working with IIS 4.0, you probably code with ASP

in ASP, we can display record 1-20 in first page, and 21-40 in second page, and so on.

it goes like this:

<%
myConn = "DSN=myDSN;UID=SA;PWD=;"

myPage= Request("WhichPage")
if myPage="" then
   myPage = 1
End if
mypagesize=request("pagesize")
If  mypagesize="" then
   mypagesize=10
end if
mySQL=request("SQLquery")
IF  mySQL="" THEN
   mySQL=SQLtemp
END IF

set rstemp=Server.CreateObject("ADODB.Recordset")
rstemp.cursorlocation=aduseclient
rstemp.cachesize=5
rstemp.open mySQL,connectme
rstemp.movefirst
rstemp.pagesize=mypagesize
maxpages=cint(rstemp.pagecount)
maxrecs=cint(rstemp.pagesize)
rstemp.absolutepage=mypage
howmanyrecs=0
howmanyfields=rstemp.fields.count -1
response.write "Page " & mypage & " of " & maxpages & "<br>"
response.write "<table border='1'><tr>"

'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
   response.write "<td><b>" & rstemp(i).name & "</b></td>"
NEXT
response.write "</tr>"

' Now loop through the data
DO  UNTIL rstemp.eof OR howmanyrecs>=maxrecs
   response.write "<tr>"
   FOR i = 0 to howmanyfields
      fieldvalue=rstemp(i)
      If isnull(fieldvalue) THEN
         fieldvalue="n/a"
      END IF
      If trim(fieldvalue)="" THEN
         fieldvalue="&nbsp;"
      END IF
      response.write "<td valign='top'>"
      response.write fieldvalue
      response.write "</td>"
   next
   response.write "</tr>"
   rstemp.movenext
   howmanyrecs=howmanyrecs+1
LOOP
response.write "</table><p>"

' close, destroy
rstemp.close
set rstemp=nothing

' Now make the page _ of _ hyperlinks
Call PageNavBar

sub PageNavBar()
      pad=""
      scriptname=request.servervariables("script_name")
      response.write "<table rows='1' cols='1' width='97%'><tr>"
      response.write "<td>"
      response.write "<font size='2' color='black' face='Verdana, Arial,Helvetica, sans-serif'>"
      if (mypage mod 10) = 0 then
       counterstart = mypage - 9
      else
       counterstart = mypage - (mypage mod 10) + 1
      end if
      counterend = counterstart + 9
      if counterend > maxpages then counterend = maxpages
      if counterstart <> 1 then
       ref="<a href='" & scriptname
       ref=ref & "?whichpage=" & 1
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>First</a>&nbsp;:&nbsp;"
       Response.Write ref


       ref="<a href='" & scriptname
       ref=ref & "?whichpage=" & (counterstart - 1)
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>Previous</a>&nbsp;"
       Response.Write ref
      end if
      Response.Write "["
      for counter=counterstart to counterend
       If counter>=10 then
       pad=""
       end if
       if cstr(counter) <> mypage then
       ref="<a href='" & scriptname
       ref=ref & "?whichpage=" & counter
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>" & pad & counter & "</a>"
       else
       ref="<b>" & pad & counter & "</b>"
       end if
       response.write ref
       if counter <> counterend then response.write " "
      next
      Response.Write "]"
      if counterend <> maxpages then
       ref="&nbsp;<a href='" & scriptname
       ref=ref & "?whichpage=" & (counterend + 1)
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>Next</a>"
       Response.Write ref


       ref="&nbsp;:&nbsp;<a href='" & scriptname
       ref=ref & "?whichpage=" & maxpages
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>Last</a>"
       Response.Write ref
      end if
      response.write "<br></font>"
      response.write "</td>"
      response.write "</table>"
end sub
%>



I hope it help, please let me know information above code






0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.