July 6, 2007 at 3:34 am
Jeff,
Congratulations on being recognized by this forum for excellence.
Given table MyNames ([ID] integer primary key,[Name] varchar(50))
table numbers (num integer) (table of numbers 1-N)
This query is the foundation of an sql solution for any @NthRow
and any number of rows in MyName.
declare @NthRow int,@MaxID int
set @NthRow=5
--Use any query to return number of rows in table NyNames.
set @MaxID=(select count(*) from MyNames)
select [Index],RowID,[Name],[From],[To]
from
(select
num as [Index],((num-1)*@NthRow)+1 as [From],num*@NthRow as [To]
from numbers
where num between 1 and @MaxID) as A
join
(select [Name],row_number()over(order by [Name]) as RowID
from MyNames) as B
on RowID between [From] and [To]
I do all development in the D4 Dataphor language and use sql server
as a data store. When appropriate I use pass-through queries in
sql. I urge people to look at D4. Check out my blog to help rap
your head around a relational language that has previously never
existed. I'll help anyone interested in getting started with it.
This is not trival stuff but well worth the effort. It is high
grade brain candy. Use sql excellence as a stepping stone to the
next level.
best,
July 6, 2007 at 8:11 am
Rog,
I appreciate the compliment. A long time ago, I realized that the first step towards the "Sql excellence" you speak of is correct identification of the given problem...
Apparently, though, you've not achieved that same level... you posted code for the wrong question and that makes me not trust you or your product for any form of "excellence". The OP isn't looking for an Nth row solution as you posted, the OP is looking for a hierarchical tree resolver using an adjacency model that goes to the Nth level. Worse than that, this is an SQL Server 2000 forum... SQL Server 2000 doesn't even have the Row_Number() function nor any direct equivelent.
Please stop spamming this forum to sell product. Contact the owners of the forum and do it the right way by submitting a "product review" like everyone else does. Probably won't cost a dime and you might even get some customers and referrals from it. All the spam and personal brown-nosing does is turn folks like me right off.
--Jeff Moden
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply