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

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

[Back to original message]