CODEWITHSUNDEEP
sqlsybasesqlanywhere
Heather
Heather

Reputation: 989

SQL Anywhere: find rows that are +-2 compared to another row

I have the following table:

ID  User  Form  Depth
1   A     ABC   2001
1   A     XYZ   1001
1   B     XYZ   1003
1   B     DEF   3001
1   C     XYZ   1000

If ID and Form are identical, I need to identify those rows that are +-2 from User A. Using the example above, the script would return:

ID  User  Form  Depth
1   B     XYZ   1003
1   C     XYZ   1000

I already have a script which identifies rows with identical ID and Form--I just need the other part, but I'm struggling with figuring out the logic. I was hoping there was some kind of DIFF function I could use, but I can't find one for SQL Anywhere.

Does anyone have any suggestions?

Thanks!

Upvotes: 1

Views: 92

Answers (3)

Edward
Edward

Reputation: 762

A quick and dirty generalized method.

Replace @User with whomever you would like to remove. 

DECLARE @table TABLE ( 
    ID Int
    ,[User] VARCHAR(2) 
    ,Form VARCHAR(3)
    ,Depth INT 
) 

DECLARE @User VARCHAR(2) = 'A' 

INSERT INTO @table (ID , [User], Form, Depth)
VALUES 
    (1 , 'A' , 'ABC' , 2001),
    (1 , 'A' , 'XYZ' , 1001),
    (1 , 'B' , 'XYZ' , 1003),
    (1 , 'B' , 'DEF' , 3001),
    (1 , 'C' , 'XYZ' , 1000)

SELECT t1.ID, t1.[User], t1.Form, t1.Depth , ROW_NUMBER() OVER(ORDER BY t1.ID, t1.[User], t1.Form, t1.Depth) AS [row_number] 
INTO #temp 
FROM @table as t1 
INNER JOIN ( 
    SELECT t.ID, t.Form, COUNT('8') as [count] 
    FROM @table as t
    GROUP BY ID, Form 
    HAVING COUNT('8') > 1 
) as duplicates
ON duplicates.ID = t1.ID 
AND duplicates. Form = t1.Form 
ORDER BY ID, User, Form, Depth


-- SELECT * FROM #temp 

SELECT [row_number] - 2 as value 
INTO #range 
FROM #temp as t
WHERE t.[User] = @User


--SELECT * FROM #range

INSERT INTO #range 
SELECT [row_number] - 1 
FROM #temp as t 
WHERE t.[User] = @User

INSERT INTO #range 
SELECT [row_number] + 1 
FROM #temp as t
WHERE t.[User] = @User

INSERT INTO #range 
SELECT [row_number] + 2
FROM #temp as t 
WHERE t.[User] = @User

SELECT * FROM #temp 
WHERE [row_number] IN (SELECT value FROM #range)


DROP TABLE #temp 
DROP TABLE #range

Upvotes: 1

markp-fuso
markp-fuso

Reputation: 35256

If you're looking for the depth to be exactly +/-2 from A's depth:

select t1.*
from   mytab t1,
       mytab t2
where  t1.id    = t2.id
and    t1.form  = t2.form
and    t1.user != 'A'
and    t2.user  = 'A'
and    abs(t1.depth - t2.depth) = 2
go

ID  User  Form  Depth
--- ----- ----- -----
1   B     XYZ   1003

If you're looking for the depth to be within 2 of A's depth (ie, diff <= 2):

select t1.*
from   mytab t1,
       mytab t2
where  t1.id    = t2.id
and    t1.form  = t2.form
and    t1.user != 'A'
and    t2.user  = 'A'
and    abs(t1.depth - t2.depth) <= 2
go

ID  User  Form  Depth
--- ----- ----- -----
1   B     XYZ   1003
1   C     XYZ   1000

This is pretty basic SQL so while this fiddle was done with MySQL, you should find the queries work in SQLAnywhere, too: sql fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

I think you want exists:

select t.*
from t
where t.user <> 'A' and
      exists (select 1
              from t t2
              where t2.form = t.form and t2.id = t.id and
                    t2.depth between t.depth - 2 and t.depth + 2
             );

Upvotes: 1

Related Questions