Question :
SELECT *
FROM SameLogTable
WHERE ID_Table IN (SELECT ID_Table-1
FROM SameLogTable
WHERE <SameCondition>) OR
ID_Table IN (SELECT ID_Table
FROM SameLogTable
WHERE <SameCondition>) OR
ID_Table IN (SELECT ID_Table+1
FROM SameLogTable
WHERE <SameCondition>)
This query runs on a Logging Table, and I want to select particular events, but also the event preceding and following those events.
This solution feels ugly and inefficient, what would be the better way to write the for this?
Example: If I am interested in lines with ID 4, and 23 of a LogTable, I want to get the following result:
ID Column1 Column2 ...
3 ... ...
4 ... ...
5 ... ...
22 ... ...
23 ... ...
24 ... ...
These are all lines from the Same LogTable, except I specify Lines 4 and 23 using a WHERE , and I want the Select to automatically return rows 3,5 for 4 and rows 22, 24 for 23.
To Summarize the results:
MyQuery: 16s
UNION ALL: 4s
Join: ~0s
Thanks for the replies!
Answer :
Assuming that ID
is the primary key of the table (or has a unique constraint), you can use this variant:
SELECT t.*
FROM SameLogTable AS t
JOIN ( SELECT id
FROM SameLogTable
WHERE <SameCondition>
) AS c
ON t.id = c.id -1
OR t.id = c.id
OR t.id = c.id +1 ;
Note: if the id
have gaps, which is very probable, the above will not work as expected and neither will your original query. ROW_NUMBER()
can help you for that:
; WITH cte AS
( SELECT t.*,
rn = ROW_NUMBER() OVER (ORDER BY t.id)
FROM SameLogTable AS t
)
SELECT t.*
FROM cte AS t
JOIN ( SELECT rn
FROM cte
WHERE <SameCondition>
) AS c
ON t.rn = c.rn - 1
OR t.rn = c.rn
OR t.rn = c.rn + 1 ;
On solution is to use a UNION ALL
so you only need a single sub-query:
SELECT *
FROM SameLogTable
WHERE ID_Table in (SELECT ID_Table-1
FROM SameLogTable
WHERE <SameCondition>
UNION ALL
SELECT ID_Table
FROM SameLogTable
WHERE <SameCondition>
UNION ALL
SELECT ID_Table+1
FROM SameLogTable
WHERE <SameCondition>)