Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Problem

Author

Topic

alexc
Starting Member

10 Posts

Posted - 2002-09-17 : 11:18:15

I am trying to perform the a query on the following data:

Row_ID Step_No DateTimeStamp
1 0 09/17/2002 12:01:00
2 0 09/17/2002 12:02:00 <-- return
3 1 09/17/2002 12:03:00 <-- return
4 2 09/17/2002 12:04:00
5 3 09/17/2002 12:05:00
6 4 09/17/2002 12:06:00
7 0 09/17/2002 12:07:00
8 0 09/17/2002 12:08:00
9 0 09/17/2002 12:09:00 <-- return
10 1 09/17/2002 12:10:00 <-- return
11 2 09/17/2002 12:11:00
12 0 09/17/2002 12:12:00 <-- return
13 1 09/17/2002 12:13:00 <-- return

I would like a query that would only return the records where step_no = 0 and the next record that does not equal 0.

Any help you provide would be great.

Edited by - alexc on 09/17/2002 11:19:23

Teroman
Posting Yak Master

115 Posts

Posted - 2002-09-17 : 11:32:58

there are a bunch of ways of doing this involving cursors (BOO!!!) and sub queries (boo) but the way i prefer involves row ids

usually i create a temp table with an identity column, and insert into that, but if your data is gauranteed to be in order with contiguous Row_IDs then you can skip this part

just try

select A.*
from myTable A
left join myTable B
on A.Row_ID = B.Row_ID -1
where A.Step_No = 0
and isnull(B.Step_No, 1) <> 0

the outer join and isnull() should make sure you get the last row if it fits the criteria

hope this works ok

col

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-17 : 11:33:06

OK, well first, it must be said that there is no concept of 'next' in relational theory. RDBMSs deal with sets of data the order of which is only dicated by an ORDER BY clause.

Now, having said that, I assume by next you really mean ID + 1, in which case a NOT EXISTS with a correlated subquery should do the trick ...


select
    <col_list>
from
    <table_name> t
where
   (t.Step_no = 0 and
    not exists (
        select 47
        from
            <table_name>
        where
            row_id = t.row_id + 1 and
            Step_no = 0) ) or
    exists (
        select 47
        from
            <table_name>
        where
            row_id = t.row_id - 1 and
            Step_no = 0)

Jay White
{0}

alexc
Starting Member

10 Posts

Posted - 2002-09-17 : 11:49:52

Ok, this returns all the records where the step_no = 0 right before it changes to 1, but is there a way to return the record that has the step_no = 1 as well?

The result set would look like this:
step_no
0
1
0
1
0
1

Thanks

quote:

there are a bunch of ways of doing this involving cursors (BOO!!!) and sub queries (boo) but the way i prefer involves row ids

usually i create a temp table with an identity column, and insert into that, but if your data is gauranteed to be in order with contiguous Row_IDs then you can skip this part

just try

select A.*
from myTable A
left join myTable B
on A.Row_ID = B.Row_ID -1
where A.Step_No = 0
and isnull(B.Step_No, 1) <> 0

the outer join and isnull() should make sure you get the last row if it fits the criteria

hope this works ok

col

JBoals
Starting Member

1 Post

Posted - 2002-09-17 : 12:47:58

Someone'll likely have more efficient code but best I could do on short notice

 
declare @Row1 int, @Step1 int, @dt1 DateTime, 
  @Row2 int, @Step2 int, @dt2 DateTime        
declare @HoldStep int                         
                                              
declare @t table                              
   (Row_ID int,                               
   Step_No int,                               
   DateTimeStamp DateTime)                    
                                              
declare c cursor for                          
  select * from steps                         
                                              
open c                                        
                                              
Fetch next from c into @Row1, @Step1, @dt1    
set @HoldStep=@step1                          
While @@FETCH_STATUS = 0                      
begin                                         
  if @HoldStep = 0                            
  begin                                       
    -- get another row                        
    Fetch next from c into @Row2, @Step2, @dt2
    if @Step2 <> 0                            
    begin                                     
      -- put both rows in the temp table      
      insert @t values(@Row1, @Step1, @dt1)   
      insert @t values(@Row2, @Step2, @dt2)   
      set @HoldStep=-1                        
    end                                       
    else                                      
      -- make 2nd row the new current row     
    begin                                     
      set @Row1 = @Row2                       
      set @Step1 = @Step2                     
      set @dt1 = @dt2                         
      set @HoldStep=@step1                    
    end                                       
  end                                         
  else                                        
  begin                                       
    Fetch next from c into @Row1, @Step1, @dt1
    set @HoldStep=@step1                      
  end                                         
end                                           
                                              
CLOSE c                                       
DEALLOCATE c                                  
  
-- display the results                                            
select * from @t

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-17 : 13:00:54

DAMN IT.

Nothing bugs me more than when I post a working solution (and especially a good set based solution showing a subtree cost of 0.0539) and the thread continues on like I never posted .... Did you read my post? Why do I even bother ...


create table alexc (
    Row_ID int not null primary key,
    Step_No int,
    DateTimeStamp smalldatetime )
go
insert alexc (Row_ID, Step_No, DateTimeStamp)
select 1,0,'09/17/2002 12:01:00'
union select 2,0,'09/17/2002 12:02:00'  
union select 3,1,'09/17/2002 12:03:00'  
union select 4,2,'09/17/2002 12:04:00' 
union select 5,3,'09/17/2002 12:05:00' 
union select 6,4,'09/17/2002 12:06:00' 
union select 7,0,'09/17/2002 12:07:00' 
union select 8,0,'09/17/2002 12:08:00' 
union select 9,0,'09/17/2002 12:09:00'
union select 10,1,'09/17/2002 12:10:00'  
union select 11,2,'09/17/2002 12:11:00' 
union select 12,0,'09/17/2002 12:12:00'  
union select 13,1,'09/17/2002 12:13:00'
go

select
    Row_id,
    Step_no,
    DateTimeStamp
from
    Alexc t
where
   (t.Step_no = 0 and
    not exists (
        select 47
        from
            Alexc
        where
            row_id = t.row_id + 1 and
            Step_no = 0) ) or
    (t.Step_no <> 0 and -- I forgot this piece in the original
    exists (
        select 47
        from
            Alexc
        where
            row_id = t.row_id - 1 and
            Step_no = 0))
go

Jay White
{0}

alexc
Starting Member

10 Posts

Posted - 2002-09-17 : 13:08:58

Thanks for your help. This works great. I have one more question for you though. The current query retrieves the last 0 in the set of 0's, is there a way to retrieve the first 0?

Any help would be great.

quote:

DAMN IT.


create table alexc (
    Row_ID int not null primary key,
    Step_No int,
    DateTimeStamp smalldatetime )
go
insert alexc (Row_ID, Step_No, DateTimeStamp)
select 1,0,'09/17/2002 12:01:00'
union select 2,0,'09/17/2002 12:02:00'  
union select 3,1,'09/17/2002 12:03:00'  
union select 4,2,'09/17/2002 12:04:00' 
union select 5,3,'09/17/2002 12:05:00' 
union select 6,4,'09/17/2002 12:06:00' 
union select 7,0,'09/17/2002 12:07:00' 
union select 8,0,'09/17/2002 12:08:00' 
union select 9,0,'09/17/2002 12:09:00'
union select 10,1,'09/17/2002 12:10:00'  
union select 11,2,'09/17/2002 12:11:00' 
union select 12,0,'09/17/2002 12:12:00'  
union select 13,1,'09/17/2002 12:13:00'
go

select
    Row_id,
    Step_no,
    DateTimeStamp
from
    Alexc t
where
   (t.Step_no = 0 and
    not exists (
        select 47
        from
            Alexc
        where
            row_id = t.row_id + 1 and
            Step_no = 0) ) or
    (t.Step_no <> 0 and -- I forgot this piece in the original
    exists (
        select 47
        from
            Alexc
        where
            row_id = t.row_id - 1 and
            Step_no = 0))
go

Jay White
{0}

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-17 : 13:13:18

sure...


select
    Row_id,
    Step_no,
    DateTimeStamp
from
    Alexc t
where
   (t.Step_no = 0 and
    not exists (
        select 47
        from
            Alexc
        where
            row_id = t.row_id - 1 and
            Step_no = 0) ) or
    (t.Step_no <> 0 and
    exists (
        select 47
        from
            Alexc
        where
            row_id = t.row_id - 1 and
            Step_no = 0))
go

Jay White
{0}

alexc
Starting Member

10 Posts

Posted - 2002-09-17 : 13:16:25

Thanks.

quote:

sure...


select
    Row_id,
    Step_no,
    DateTimeStamp
from
    Alexc t
where
   (t.Step_no = 0 and
    not exists (
        select 47
        from
            Alexc
        where
            row_id = t.row_id - 1 and
            Step_no = 0) ) or
    (t.Step_no <> 0 and
    exists (
        select 47
        from
            Alexc
        where
            row_id = t.row_id - 1 and
            Step_no = 0))
go

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources