April 18, 2007 at 8:53 pm
Can someone help me with a solution for this.
Say I have a table with columns a, b, c, d. now lets say column c holds consecutive values like 1,2,3 ...
How can I select the row that follow consecutively, and if there is an enterval in the value like value jumping from 6 to 9, I need to select from 9,10... to the nwxt row?
Someone please.
April 18, 2007 at 11:37 pm
I'm not dure if I understand 100% my first answer is order asc by colomn c. If that is not what you meant and you want to loop through the records you could use a cursor or a while and still order by colomn c. if you are having a problem with selecting the nex row based on the numbers like 6..9 then declare a vairable and after eac select set it equal to the value in colomn c then in your select you will select the next min value in colomn c that is greater than the vairable.
Hope this helps.
April 20, 2007 at 9:39 am
This may help get you going in the right direction. I recently learned this handy trick thanks to this site.
-- Create a source table with IDs missing
-- to mimic user's situation
declare @Source table (SourceID int)
insert
into @Source values (1)
insert into @Source values (2)
insert into @Source values (3)
insert into @Source values (7)
insert into @Source values (10)
insert into @Source values (11)
insert into @Source values (12)
insert into @Source values (15)
insert into @Source values (19)
insert into @Source values (25)
-- Create a "comparison" table that
-- contains contiguous numbers
declare @Comparison table (ComparisonID int)
-- Only seed the Comparison table
-- based on the highest ID in our
-- source table
declare @MaxCounter int
select @MaxCounter = max(SourceID)
from @Source
declare @Counter int
set @Counter = 1
while @Counter <= @MaxCounter
begin
insert into @Comparison values (@Counter)
set @Counter = @Counter + 1
end
-- Now do a left join to figure out what
-- the missing IDs are
select c.ComparisonID
from @Comparison c
left join @Source s on c.ComparisonID = s.SourceID
where s.SourceID is null
Hope this helps!
April 20, 2007 at 9:55 am
Maybe this
SELECT a.c As [ThisRow],
(SELECT MIN(b.c) FROM b WHERE b.c > a.c) As [NextRow]
FROM a
Far away is close at hand in the images of elsewhere.
Anon.
April 20, 2007 at 4:43 pm
Thanks LSAdvantage, that really went a long way.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply