slim88 slim88 - 3 months ago 19
SQL Question

SQL/Teradata: Return records where value in consecutive rows is the same

I have a data set that looks like:

ID date emp_num loc
1111 5/2/16 111111 Brooklyn
1112 5/3/16 222222 Detroit
1113 5/3/16 333333 San Diego
1114 5/2/16 333333 Orlando
1115 5/5/16 333333 Brooklyn
1116 5/7/16 111111 Orlando


In this case, I would want to return records 1113, 1114, and 1115 because the emp_num in consecutive rows (ordered by ID) is the same.

I use Teradata, but if anyone has a SQL solution for another engine I can usually manage to translate it.

Thank you.

Answer

You need to look at the previous/next row and check if it didn't change:

SELECT * 
FROM tab
QUALIFY 
   MIN(emp_num) --previous row
   OVER (ORDER BY ID
         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = emp_num
OR
   MIN(emp_num) -- next row
   OVER (ORDER BY ID
         ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) = emp_num

In Standard SQL this would be a task for LAG/LEAD, but Teradata doesn't impement it, so you have to rewrite it.