Manoj Kumar Manoj Kumar - 1 year ago 46
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 Source

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
;With CTE as
select *,case when CHARINDEX('00',reverse(col1))>0 then 1 end 
End00 from @t
,CTE1 as
select,a.col1  from cte A
where exists
(select id from cte b where and b.end00 is not null)
and CHARINDEX('11',reverse(a.col1))<=0


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