Sunday, June 11, 2006 8:24 AM
I need to select the no of records on the basis of specified range of records.
In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..
I have used temporary table,map the table primary key to the next table with identity
but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...
Is there some other best way to extract the specified data
here is the type of query.
select * from customers where rownum between 1000 and 10000
this is in oracle
i am in need to do this in the sql server
waiting for the response...............................
Sunday, June 11, 2006 8:27 AMThere is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.
Sunday, June 11, 2006 4:20 PMIf you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:select *from (select *, row_number() over(order by CustomerId) as rownumfrom customers) as cwhere c.rownum between 1000 and 10000;If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:declare @n intset @n = 1000 -- set number of rows you want to insert at a timeset rowcount @nwhile(1=1)begininsert into MasterCustomersselect ...from Customers as c1where not exists(select * from MasterCustomers as c2where c2.CustomerName = c1.CustomerName)if @@rowcount = 0 breakendset rowcount 0-- using TOPdeclare @n intset @n = 1000 -- set number of rows you want to insert at a timewhile(1=1)begininsert top(@n) into MasterCustomersselect ...from Customers as c1where not exists(select * from MasterCustomers as c2where c2.CustomerName = c1.CustomerName)if @@rowcount = 0 breakend
Sunday, June 11, 2006 10:43 PMSQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
select * from
(select top 1000 * from (
select top 11000 *
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...
for extract customers betweeen 10000 and 11000 based on customer_id
Saturday, October 14, 2006 1:56 AM
if you use sql2005
you also can use the function ROW_NUMBER() to
select rownum between 1000 and 10000 like oracle
with customers _temp as
(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers _temp
where RowNumber between 1000 and 10000