Manoj Kumar Manoj Kumar - 7 months ago 5
SQL Question

Select column values from DB for which the subsequent row does not have a specified value

I have a table say MyTable has two columns Id, Data and has following records in it:


  1. ABCDE00

  2. DEFGH11

  3. CCCCC21

  4. AAAAA00

  5. BBBBB10

  6. vvvvv00

  7. xxxxx88



Now what I want that all the records which have end with string '00' and does not have subsequent row having column ending with '11' . So my output using this condition should be like this:
1. AAAAA00
2. vvvvv00

Any help would be appreciated.

Answer

if anyone is not using sql server 2012,then they an try this

declare @t table(id int identity(1,1),col1 varchar(100))
insert into @t values
('ABCDE00')
,('DEFGH11')
,('CCCCC21')
,('AAAAA00')
,('BBBBB10')
,('vvvvv00')
,('xxxxx88')
;With CTE as
(
select *,case when CHARINDEX('00',reverse(col1))>0 then 1 end 
End00 from @t
)
,CTE1 as
(
select a.id,a.col1  from cte A
where exists
(select id from cte b where  a.id=b.id+1 and b.end00 is not null)
and CHARINDEX('11',reverse(a.col1))<=0

)

select a.id,a.col1  from cte A
where exists
(select id from cte1 b where  a.id=b.id-1 )