Hi.

How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?

thanks

"pbd22" <dushkin@gmail.com> wrote in message
news:1176393322.377665.40770@y5g2000hsa.googlegrou ps.com...
> Hi.
>
> How does one return a range of rows.
> I know that "Top 5" will return rows 0 - 5
> but, how do I get 6 - 10?

Depends partly on which version of SQL Server you're using.

2000 or 2005?

2000, you need something like

select top 5 orderid from dbo.orders where orderid in
(select top 10 orderid from dbo.orders order by orderid) order by orderid
desc

for 2005 something like:


with OrdersCTE as
(
select row_number() over (order by orderid) as rownum, *
from dbo.orders
)

select * from OrdersCTE where rownum between 5 and 10

(obviously don't use * in your actual production code)

>
> thanks
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

On Apr 12, 8:55 am, "pbd22" <dush...@gmail.com> wrote:
> Hi.
>
> How does one return a range of rows.
> I know that "Top 5" will return rows 0 - 5
> but, how do I get 6 - 10?
>
> thanks


Hi.

I am following up my own message with more detailed info.
If some """kind""" soul could tell me how to isolate a range
of rows for each of the following conditions I would really
(**really**)
apprecaite it. The SQL is dynamically generated based on user
requirements. The resulting statements vary wildly but, the below
three
look more-or-less like the rest of them:

I REALLY appreciate your help. Thanks!

-----------------------------------------------------------------------------------------------------------------

EXAMPLE ONE (ORDERBY CASE STATEMENT):

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from
( select distinct postalAddress from Profiles union select distinct
name from ProfilesPictures union select distinct postaladdress from
ChangeSettings union select distinct postaladdress from LastSignIn )
drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.sex='men' and
tab1.bdayyear between '0' and '1988' and tab2.photodefault = 1 or
tab2.nophoto = 1 order by CASE WHEN userID=67 THEN 1 WHEN userID=103
THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100
THEN 14 WHEN userID=96 THEN 15 WHEN userID=70 THEN 16 WHEN userID=98
THEN 17 WHEN userID=69 THEN 18 WHEN userID=92 THEN 19 WHEN userID=93
THEN 20 WHEN userID=68 THEN 21 WHEN userID=65 THEN 22 WHEN userID=86
THEN 23 WHEN userID=91 THEN 24 WHEN userID=80 THEN 25 WHEN userID=76
THEN 26 WHEN userID=73 THEN 27 WHEN userID=72 THEN 28 WHEN userID=74
THEN 29 WHEN userID=83 THEN 30 WHEN userID=84 THEN 31 WHEN userID=88
THEN 32 WHEN userID=75 THEN 33 WHEN userID=77 THEN 34 WHEN userID=78
THEN 35 WHEN userID=79 THEN 36 WHEN userID=61 THEN 37 WHEN userID=101
THEN 38 WHEN userID=97 THEN 39 WHEN userID=99 THEN 40 WHEN userID=95
THEN 41 WHEN userID=71 THEN 42 WHEN userID=85 THEN 43 WHEN userID=62
THEN 44 END ;

EXAMPLE TWO (ORDERBY "SOME COLUMN"... COULD BE DATE, TIME, NUMBER,
ETC. THIS IS ALWAYS ASCENDING):

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
tab4.online from ( select distinct postalAddress from Profiles union
select distinct name from ProfilesPictures union select distinct
postaladdress from ChangeSettings union select distinct postaladdress
from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
between '0' and '1988' and tab2.photodefault = 1 or tab2.nophoto = 1
order by tab1.registerDate ;

EXAMPLE THREE (ORDERBY LAST TIME LOGGED IN - ALWAYS DESCENDING):

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
tab4.online from ( select distinct postalAddress from Profiles union
select distinct name from ProfilesPictures union select distinct
postaladdress from ChangeSettings union select distinct postaladdress
from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
between '0' and '1989' and tab2.photodefault = 1 and not tab2.nophoto
= 1 order by tab4.signindate DESC ;

On 12 Apr 2007 11:46:57 -0700, pbd22 wrote:

>I am following up my own message with more detailed info.
>If some """kind""" soul could tell me how to isolate a range
>of rows for each of the following conditions I would really
>(**really**)
>apprecaite it.

Hi pbd22,

Some kind sould has even written a whole web page full of methods to
achieve this. The first part of the page focuses on ASP techniques, but
the second part covers DB techniques for paging.

http://databases.aspfaq.com/database...recordset.html

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Apr 12, 11:46 am, "pbd22" <dush...@gmail.com> wrote:
> On Apr 12, 8:55 am, "pbd22" <dush...@gmail.com> wrote:
>
> > Hi.
>
> > How does one return a range of rows.
> > I know that "Top 5" will return rows 0 - 5
> > but, how do I get 6 - 10?
>
> > thanks
>
> Hi.
>
> I am following up my own message with more detailed info.
> If some """kind""" soul could tell me how to isolate a range
> of rows for each of the following conditions I would really
> (**really**)
> apprecaite it. The SQL is dynamically generated based on user
> requirements. The resulting statements vary wildly but, the below
> three
> look more-or-less like the rest of them:
>
> I REALLY appreciate your help. Thanks!
>
> -----------------------------------------------------------------------------------------------------------------
>
> EXAMPLE ONE (ORDERBY CASE STATEMENT):
>
> select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
> tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
> tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
> tab2.photolocation , tab2.photoname , tab2.photodefault ,
> tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from
> ( select distinct postalAddress from Profiles union select distinct
> name from ProfilesPictures union select distinct postaladdress from
> ChangeSettings union select distinct postaladdress from LastSignIn )
> drv Left Join Profiles tab1 on (drv.postalAddress =
> tab1.postalAddress) Left Join ProfilesPictures tab2 on
> (drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
> (drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
> on (drv.postalAddress = tab3.postaladdress) where tab1.sex='men' and
> tab1.bdayyear between '0' and '1988' and tab2.photodefault = 1 or
> tab2.nophoto = 1 order by CASE WHEN userID=67 THEN 1 WHEN userID=103
> THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
> THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
> THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
> THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100
> THEN 14 WHEN userID=96 THEN 15 WHEN userID=70 THEN 16 WHEN userID=98
> THEN 17 WHEN userID=69 THEN 18 WHEN userID=92 THEN 19 WHEN userID=93
> THEN 20 WHEN userID=68 THEN 21 WHEN userID=65 THEN 22 WHEN userID=86
> THEN 23 WHEN userID=91 THEN 24 WHEN userID=80 THEN 25 WHEN userID=76
> THEN 26 WHEN userID=73 THEN 27 WHEN userID=72 THEN 28 WHEN userID=74
> THEN 29 WHEN userID=83 THEN 30 WHEN userID=84 THEN 31 WHEN userID=88
> THEN 32 WHEN userID=75 THEN 33 WHEN userID=77 THEN 34 WHEN userID=78
> THEN 35 WHEN userID=79 THEN 36 WHEN userID=61 THEN 37 WHEN userID=101
> THEN 38 WHEN userID=97 THEN 39 WHEN userID=99 THEN 40 WHEN userID=95
> THEN 41 WHEN userID=71 THEN 42 WHEN userID=85 THEN 43 WHEN userID=62
> THEN 44 END ;
>
> EXAMPLE TWO (ORDERBY "SOME COLUMN"... COULD BE DATE, TIME, NUMBER,
> ETC. THIS IS ALWAYS ASCENDING):
>
> select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
> tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
> tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
> tab2.photolocation , tab2.photoname , tab2.photodefault ,
> tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
> tab4.online from ( select distinct postalAddress from Profiles union
> select distinct name from ProfilesPictures union select distinct
> postaladdress from ChangeSettings union select distinct postaladdress
> from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
> tab1.postalAddress) Left Join ProfilesPictures tab2 on
> (drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
> (drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
> on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
> between '0' and '1988' and tab2.photodefault = 1 or tab2.nophoto = 1
> order by tab1.registerDate ;
>
> EXAMPLE THREE (ORDERBY LAST TIME LOGGED IN - ALWAYS DESCENDING):
>
> select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
> tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
> tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
> tab2.photolocation , tab2.photoname , tab2.photodefault ,
> tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
> tab4.online from ( select distinct postalAddress from Profiles union
> select distinct name from ProfilesPictures union select distinct
> postaladdress from ChangeSettings union select distinct postaladdress
> from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
> tab1.postalAddress) Left Join ProfilesPictures tab2 on
> (drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
> (drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
> on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
> between '0' and '1989' and tab2.photodefault = 1 and not tab2.nophoto
> = 1 order by tab4.signindate DESC ;


Thanks Greg!

I "am" using 2005 and the solution looks much easier than the 2000
alternatives.
I am not sure why, but the second message I posted in this thread was
right after the first but it appeared after your response. Regardless,
as you can see my queries are somewhat complicated and I am not quite
sure how to apply your 2005 solution (OrdersCTE) to them. I am
wondering if you could use one of my queries to demonstrate how it
works (I hope I am not being too demanding)?
It would help me get my mind wrapped around things.

Thanks kindly for your time.

Peter

pbd22 (dushkin@gmail.com) writes:
> I "am" using 2005 and the solution looks much easier than the 2000
> alternatives.
> I am not sure why, but the second message I posted in this thread was
> right after the first but it appeared after your response. Regardless,
> as you can see my queries are somewhat complicated and I am not quite
> sure how to apply your 2005 solution (OrdersCTE) to them. I am
> wondering if you could use one of my queries to demonstrate how it
> works (I hope I am not being too demanding)?
> It would help me get my mind wrapped around things.

WITH CTE (
SELECT <yourselectlistgoeshere>,
rownum = row_number() over( ORDER BY <yourorderbylisthere>)
FROM <yourfrom+joinshere>
WHERE <anywhereclauseyoumayhav>
)
SELECT <yourselectlisthereagain, nowwithoutaliases>
FROM CTE
WHERE rownum BETWEEN @first AND @last
ORDER BY rownum


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Apr 12, 1:38 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On 12 Apr 2007 11:46:57 -0700, pbd22 wrote:
>
> >I am following up my own message with more detailed info.
> >If some """kind""" soul could tell me how to isolate a range
> >of rows for each of the following conditions I would really
> >(**really**)
> >apprecaite it.
>
> Hi pbd22,
>
> Some kind sould has even written a whole web page full of methods to
> achieve this. The first part of the page focuses on ASP techniques, but
> the second part covers DB techniques for paging.
>
> http://databases.aspfaq.com/database...ugh-a-recordse...
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Thanks Hugo (horay for kind souls).
I will go through this in detail after I get home. But, for now,
I am wondering if it is more cost-effective to do the paging logic
on the client or the server? I have already written all my paging
logic
in javascript (basically, what that link provides, but in javascript).
Do
I save on performance by moving the paging away from the server
or do you think it matters?

Thanks again.

"pbd22" <dushkin@gmail.com> wrote in message
news:1176412970.235762.124510@n76g2000hsh.googlegr oups.com...
>
> Thanks Hugo (horay for kind souls).
> I will go through this in detail after I get home. But, for now,
> I am wondering if it is more cost-effective to do the paging logic
> on the client or the server? I have already written all my paging
> logic
> in javascript (basically, what that link provides, but in javascript).
> Do
> I save on performance by moving the paging away from the server
> or do you think it matters?

Depends, how many rows are you returning to the client?

You generally want to return as little data to the client as necessary. Of
course "necessary" changes for every application.


>
> Thanks again.
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

pbd22 wrote:

> select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
> tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
> tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
> tab2.photolocation , tab2.photoname , tab2.photodefault ,
> tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from

Why the hell doesn't anyone format their queries for readability?

> order by CASE WHEN userID=67 THEN 1 WHEN userID=103
> THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
> THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
> THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
> THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100

This should be driven by a Users table. Do you really want to mess
around with altering the query every time a user is added/removed?

pbd22 (dushkin@gmail.com) writes:
> I will go through this in detail after I get home. But, for now,
> I am wondering if it is more cost-effective to do the paging logic
> on the client or the server? I have already written all my paging
> logic in javascript (basically, what that link provides, but in
> javascript). Do I save on performance by moving the paging away from the
> server or do you think it matters?

Disclaimer: I have no personal experience of writing web apps, so take this
as a grain of salt.

If you can be sure that the search can never return more than, say, 1000
rows (and this can be acieved by using TOP), reading all rows in one go,
and then page from the web server is likely to be better, since else
there would be an access to the database each time the user presses next.
But if the search could hit tens of thousands of rows, you need to have
some sort of batching mechanism, because if 100 users do that at the same
time, your web server will choke.

And you should not send all rows at once to the browser, unless the user
requests to see all rows. If the browser is on a slow connection, that
can be painful.

Finally: don't forget to give the user the option to see at least 100 items
at once. I hate sites where I only get a spoonful at a time.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx