Tricky SQL - non-contiguous number selection

Given this data on SQL Server 2005:

SectionID Name  
1         Dan  
2         Dan  
4         Dan  
5         Dan  
2         Tom  
7         Tom  
9         Tom  
10        Tom  

How can I select entries in which the section identifier should be + -2 or more from another section with the same name.

Result:

1 Dan  
4 Dan  
2 Tom  
7 Tom  
9 Tom

Thanks for reading!

+5
source share
2 answers
SELECT *
FROM mytable a
WHERE NOT EXISTS
  (SELECT *
  FROM mytable b
  WHERE a.Name = b.Name
  AND a.SectionID = b.SectionID + 1)
+3
source

Here's the LEFT JOIN Anthony answer option (removes the sequential identifier from the results)

SELECT a.*
FROM mytable a 
     LEFT JOIN mytable b ON a.Name = b.Name AND a.SectionID = b.SectionID + 1
WHERE b.SectionID IS NULL

EDIT: Since there is another interpretation of the question (just getting results where id is more than 1 number), this is another attempt to answer:

WITH alternate AS (
SELECT sectionid,
       name,
       EXISTS(SELECT a.sectionid 
              FROM mytable b 
              WHERE a.name = b.name AND 
                    (a.sectionid = b.sectionid-1 or a.sectionid = b.sectionid+1)) as has_neighbour,
       row_number() OVER (PARTITION by a.name ORDER BY a.name, a.sectionid) as row_no
FROM mytable a
)
SELECT sectionid, name 
FROM alternate
WHERE row_no % 2 = 1 OR NOT(has_neighbour) 
ORDER BY name, sectionid;

gives:

 sectionid | name 
-----------+------
         1 | Dan
         4 | Dan
         2 | Tom
         7 | Tom
         9 | Tom

: id +/- 1, , , , , .

, - , ( ).

0

All Articles