Selecting Next 50 Records - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Selecting Next 50 Records

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-09-11 : 12:10:20

Is it possible to use a SELECT to retrieve a Next 50 set of records?

Initial Query
Select top 50 email from table

What would the Query look like to retrieve the Next 50 records?

Thanks in advance.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-11 : 12:28:48

I suppose you could do
SELECT TOP 50 email from table
WHERE email not in (
SELECT top 50 email from table)

Future guru in the making.

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 12:39:02

DECLARE @email varchar(255)

SELECT TOP 50 EMAIL FROM TABLE WHERE EMAIL > @EMAIL ORDER BY EMAIL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-11 : 13:44:44

quote:
Originally posted by X002548

DECLARE @email varchar(255)

SELECT TOP 50 EMAIL FROM TABLE WHERE EMAIL > @EMAIL ORDER BY EMAIL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

What exactly does that do? I don't see where @email has been assigned a value.

Future guru in the making.

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 13:46:37

You need to save the last email from the previous select

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-11 : 14:26:43

If you're in SQL 2005

select *
from
(
select 'RowNumber' = row_number() OVER( order by email )

from table
) a

where rownumber between 51 and 100

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-11 : 14:28:47

oops
select *
from
(
select email,'RowNumber' = row_number() OVER( order by email )

from table
) a

where rownumber between 51 and 100

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-11 : 14:34:31

quote:
Originally posted by jimf

oops
select *
from
(
select email,'RowNumber' = row_number() OVER( order by email )

from table
) a

where rownumber between 51 and 100

If you are using SQL 2005, this is definitely the way to go.

By the way, do yourself a favor -- don't put column aliases in quotes, it makes them look like string literals and makes your code harder to read, especially as it get complicated.

- Jeff
http://weblogs.sqlteam.com/JeffS

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-11 : 14:41:50

By the way, do yourself a favor -- don't put column aliases in quotes, it makes them look like string literals and makes your code harder to read, especially as it get complicated.

I need to hit my hands with a ruler when I do that. It's an old bad habit that looked better to me than using <expr1> AS ColName
but what a nightmare when someone says, "we need that to be dynamic"

Jim

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 15:39:51

If this is to do with Paging to display data page-by-page see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Efficient+paging+of+recordsets,Paging+in+SQL+2005

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources