Posted by pbd22 on 04/12/07 18:46

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 ;

[Back to original message]