Retrieving records on start and end record number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieving records on start and end record number

Author

Topic

thangavelu_arun@yahoo.com
Starting Member

8 Posts

Posted - 2009-02-25 : 23:55:54

Hi,

Can canyone help me resolve this?

I need to retrieve the records based on start and end record number.

For example, if a select query returns 100 records how can i make it retrieve based on start record number (eg 12) to end record number (eg 17)?

Thanks in advance for your help.

Thanks,
Velu

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-25 : 23:59:08

SELECT * FROM TABLE WHERE RECORDNUMBER BETWEEN 12 AND 17

thangavelu_arun@yahoo.com
Starting Member

8 Posts

Posted - 2009-02-26 : 00:11:43

I'm Sorry,

I think that my questions is somewhat confusing.

My question is, if a select query returns 100 records, how to retrieve a specific number of records from a specified starting point.

For example,
select * from Table1;
results in
record1
record2
...
...
record100

And i need 5 records starting from 12'th record to 17'th record?

Thanks again for your help

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-26 : 00:33:59

try this
select recordname
from (select row_number() over(order by recordname) as rid , * from table1 )
where rid between 12 and 17

thangavelu_arun@yahoo.com
Starting Member

8 Posts

Posted - 2009-02-26 : 00:40:48

Server: Msg 195, Level 15, State 10, Line 3
'row_number' is not a recognized function name.

Its SQL Server 2000 and it doesn't recognise row_number().

I there anything that can be done in SQL Server 2000? Is there anyway to resolve this?

Thanks,
Velu

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-26 : 00:42:59

do u have any identity column in ur table

thangavelu_arun@yahoo.com
Starting Member

8 Posts

Posted - 2009-02-26 : 00:49:59

yes i do...

I need to implement pagination in the stored procedure...so i need to pass the row number and number of records and other query parameters.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-26 : 08:53:23

[code]
Select Col...
from
(Select Recordname,Col..... ,(Select Count(*) from Table Where col = t.col and IDentitycol <= t.Identitycol)as M
From Table t)Z
Where M between 12 and 17
Order by Col[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources