Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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!

share|improve this question
1  
You're just duplicating the contents of col1-colN and adding +/-1 to the ID? –  billinkc Nov 20 '13 at 13:02
1  
Give us examples of the <Condition>. Is it always equality, like id = 4 or a list check like id IN (4.23,...)? Or it can be more complicated? –  ypercube Nov 20 '13 at 13:06
1  
And which version of SQL-Server is this for? If for 2012, LEAD() and LAG() functions may help to produce a better plan. –  ypercube Nov 20 '13 at 13:08
1  
Not sure if this would improve things, but what about id_table in (select ID_Table-1 from samelogtable union all select ID_Table from samelogtable union all select ID_Table+1 from samelogtable) –  a_horse_with_no_name Nov 20 '13 at 13:57
3  
If id is IDENTITY it is not guaranteed that the values won't have gaps. So your +1/-1 might miss the actual next/previous row. –  Martin Smith Nov 20 '13 at 14:22
show 5 more comments

2 Answers

up vote 7 down vote accepted

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 ;
share|improve this answer
    
Nice, this is fast... didn't think a Join could work so nicely –  Rafael Cichocki Nov 20 '13 at 14:20
add comment

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>)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.