Querying records based on 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 2000 Forums

SQL Server Development (2000)

Querying records based on record number

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2006-09-15 : 04:09:04

Hi all

I want to query the records in a table based on the record numbers.

for example,

select * from emp where record number between 10 and 20;

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 04:26:12

This

select * from emp where [record number] between 10 and 20

?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-15 : 04:39:18

[code]Select *
from (
Select (Select Count(*) from Tbl x where x.SomeCol <= y.SomeCol) as RowNum, y.* from Tbl y
) as z
where RowNum between 10 and 20[/code]

Peter,

I think he doesn't have any [Record Number] column, otherwise why he would bother to post here

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 05:38:49

Oh, you will be surprised if you knew...

Peter Larsson
Helsingborg, Sweden

venkath
Posting Yak Master

202 Posts

Posted - 2006-09-15 : 06:35:46

quote:
Originally posted by harsh_athalye

Select *
from (
Select (Select Count(*) from Tbl x where x.SomeCol <= y.SomeCol) as RowNum, y.* from Tbl y
) as z
where RowNum between 10 and 20

Peter,

I think he doesn't have any [Record Number] column, otherwise why he would bother to post here

Harsh Athalye
India.
"Nothing is Impossible"

Hi

Thanks for ur response,
as per ur example, i have give the following query..

Select *
from (
Select (Select Count(*) from sales x where x.stor_id <= y.stor_id) as RowNum, y.* from sales y
) as z
where RowNum between 1 and 10

but i am getting the 10 records in a random way..

How to resolve it now.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-15 : 06:56:42

quote:
Originally posted by venkath

quote:
Originally posted by harsh_athalye

Select *
from (
Select (Select Count(*) from Tbl x where x.SomeCol <= y.SomeCol) as RowNum, y.* from Tbl y
) as z
where RowNum between 10 and 20

Peter,

I think he doesn't have any [Record Number] column, otherwise why he would bother to post here

Harsh Athalye
India.
"Nothing is Impossible"

Hi

Thanks for ur response,
as per ur example, i have give the following query..

Select *
from (
Select (Select Count(*) from sales x where x.stor_id <= y.stor_id) as RowNum, y.* from sales y
) as z
where RowNum between 1 and 10

but i am getting the 10 records in a random way..

How to resolve it now.

Thanks

Oh,
I forgot to add ORDER BY in the subquery ! try this...

Select * 
from (
Select (Select Count(*) from sales x where x.stor_id <= y.stor_id) as RowNum, y.* from sales y order by y.stor_id
) as z
where RowNum between 1 and 10

Harsh Athalye
India.
"Nothing is Impossible"

venkath
Posting Yak Master

202 Posts

Posted - 2006-09-15 : 08:36:43

hi

i executed the above query in pubs database, but still i am getting the following error..

Server: Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 08:42:30

Include a TOP 100 PERCENT in the subquery.

Select *
from (
Select top 100 percent (Select Count(*) from sales x where x.stor_id <= y.stor_id) as RowNum, y.* from sales y order by y.stor_id
) as z
where RowNum between 1 and 10

Peter Larsson
Helsingborg, Sweden

venkath
Posting Yak Master

202 Posts

Posted - 2006-09-15 : 09:46:13

hi

Thanks for all ur response..

but it is not giving the required results.

it is giving 10 records with every record is duplicated more than once.

Thanks'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-15 : 10:00:00

quote:
Originally posted by venkath

hi

Thanks for all ur response..

but it is not giving the required results.

it is giving 10 records with every record is duplicated more than once.

Thanks'

That's may be becuase you have duplicate Stor_IDs...Choose field in the where clause which will uniquely identify each row.

Harsh Athalye
India.
"Nothing is Impossible"

LazyDragon
Starting Member

30 Posts

Posted - 2006-09-15 : 10:01:26

quote:
Originally posted by Peso

Include a TOP 100 PERCENT in the subquery.

The Top 100 PERCENT thing is a kludge. You can achieve the same by

USE Northwind
GO

SELECT * --Use column list
FROM Orders O
WHERE (SELECT COUNT(*) FROM Orders I WHERE I.OrderID <=O.OrderID) BETWEEN 10 AND 20
ORDER BY O.OrderID

SQL server 2005 makes life lot easies with the ranking functions.

LazyDragon

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-15 : 10:28:33

Where do you want to show data?
Are you trying for pagination?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources