SQLTeam.com | Weblogs | Forums

Using calculations in Joins

Transact-SQL
#1

Hi,

I'm trying to understand why in certain cases, folks use calculations on Joins i.e comparing the primary key and then subtracting 1 in the join condition. As an example,

Input:
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+

Write an SQL query to find all numbers that appear at least three times consecutively.

Answer:

SELECT *
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

Why do we use I2.Id - 1 in this case. What is the -1 part in this l1.Id = l2.Id - 1 statement doing?

#2

Consumable test data which you should have provided:

CREATE TABLE #t
(
	id int NOT NULL PRIMARY KEY
	,num int NOT NULL
);

INSERT INTO #t
VALUES (1, 1), (2, 1), (3, 1), (4, 2)
	,(5, 1), (6, 2), (7, 2);

Using modern JOIN syntax, you can see that it joins with the previous id so that 3 consecutive numbers can be compared.

SELECT L1.num AS Num1, L2.num AS Num2, L3.num AS Num3
--SELECT DISTINCT L1.num
FROM #t L1
	JOIN #t L2
		ON L1.id = L2.id - 1
	JOIN #t L3
		ON L2.id = L3.id - 1
--WHERE L1.num = L2.num
--	AND L2.num = L3.num;

It will be more efficient not to self join but to use the LAG function. This will also work if there is a gap in the id.

WITH Nums
AS
(
	SELECT num AS Num1
		,LAG(num) OVER (ORDER BY id) AS Num2
		,LAG(num, 2) OVER (ORDER BY id) AS Num3
	FROM #t
)
SELECT DISTINCT Num1
FROM Nums
WHERE Num1 = Num2
	AND Num2 = Num3;