SQLTeam.com | Weblogs | Forums

Selecting rows based in previous row

Hi folks,
I'm hoping for some help with a query please. I have some Outbound (From) and Inbound (To) string pairs i.e. ABC (From) DEF (To) with a reversed pair following it i.e. DEF (From) ABC (To). They have sequential Numbers grouped by a Date.

My table has data that looks something like this...

Date Number From To
01/08/2019 100 ABC DEF
01/08/2019 101 DEF ABC
01/08/2019 102 ABC GHI
01/08/2019 103 GHI ABC
01/08/2019 116 ABC JKL
01/08/2019 117 JKL ABC
01/08/2019 306 ABC MNO
01/08/2019 308 ABC DEF
01/08/2019 309 DEF ABC
01/08/2019 328 ABC ABC

I am trying to return only records that are in Bold that do not have a pairing like the other rows do i.e. 306 ABC MNO has no 307 MNO ABC and 328 ABC ABC is invalid.

I have been looking at LEAD and LAG but can't find an example that fits my challenge.

Is anyone able to offer some advice please?
Thank you.

hi

i tried to do this ... hope i am correct ..
if it helps great :slight_smile: :slight_smile:

drop create data ...
drop table #data 
go 

create table #data 
(
Date1	date,
Number1	int,
From1  varchar(100),
To1 varchar(100)
)
go 

insert into #data select'01/08/2019',100,'ABC','DEF'
insert into #data select'01/08/2019',101,'DEF','ABC'
insert into #data select'01/08/2019',102,'ABC','GHI'
insert into #data select'01/08/2019',103,'GHI','ABC'
insert into #data select'01/08/2019',116,'ABC','JKL'
insert into #data select'01/08/2019',117,'JKL','ABC'
insert into #data select'01/08/2019',306,'ABC','MNO'
insert into #data select'01/08/2019',308,'ABC','DEF'
insert into #data select'01/08/2019',309,'DEF','ABC'
insert into #data select'01/08/2019',328,'ABC','ABC'
go 

select * from #data 
go
SQL ...
SELECT b.* 
FROM   (SELECT from1, 
               to1 
        FROM   #data 
        EXCEPT 
        SELECT to1, 
               from1 
        FROM   #data) a 
       JOIN #data b 
         ON a.from1 = b.from1 
            AND a.to1 = b.to1

1 Like

Thanks very much. I added the date to the SQL and it worked a treat!

SELECT Date1, Number1, From1, To1
FROM (
    SELECT *, LEAD(From1, 1) OVER(ORDER BY Number1) AS From1_Next
    FROM #data
) AS query1
WHERE To1 <> From1_Next OR From1_Next IS NULL