fetch rows when condition macthes

Question

fetch rows when condition macthes

khushbu

SSCarpal Tunnel

Points: 4591
More actions
February 5, 2019 at 1:10 am

#373742

Hi
I have a scenario where a table has column which is either Error or Info or warning.
I need to fetch two rows - 2 rows above and 2 rows below when macthing condition of error is met
like
a, info
b, info
c, info
d, error
e, info
f, info
so result shud be b, info; c, info ; d, error; e, info;f, info

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

So are you ID's letters? What happens when you get to ID z, is the next row aa? That's going to be a problem for ordering in SQL Server; when ordering a (n)varchar the characters are ordered according the the order of the characters in the Collation starting at the left most character and then moving right. So aa has a "lower" value than z.

Assuming, however, that you have another column for the time and date of the error as well (and therefore even if you suffer the above problem you have am ascending column to order by) you could do:

WITH CTE AS(
  SELECT LetterID,
     [Status],
     LAG(LetterID,2) OVER (ORDER BY DateTimeColumn) AS ID2Ago,
     LAG([Status],2) OVER (ORDER BY DateTimeColumn) AS Status2Ago,
     LAG(LetterID,1) OVER (ORDER BY DateTimeColumn) AS ID1Ago,
     LAG([Status],1) OVER (ORDER BY DateTimeColumn) AS Status1Ago,
     LEAD(LetterID,1) OVER (ORDER BY DateTimeColumn) AS ID1Next,
     LEAD([Status],1) OVER (ORDER BY DateTimeColumn) AS Status1Next,
     LEAD(LetterID,2) OVER (ORDER BY DateTimeColumn) AS ID2Next,
     LEAD([Status],2) OVER (ORDER BY DateTimeColumn) AS Status2Next
  FROM YourTable)
SELECT ID2Ago, Status2Ago,
    ID1Ago, Status1Ago,
    LetterID, [Status],
    ID1Next, Status1Next,
    ID2Next, Status2Next
FROM CTE
WHERE [Status] = 'Error';

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

drew.allen SSC Guru Points: 76944 More actions · Answer 2

I'm not sure that the OP actually wants the results pivoted. I think that this will also be faster, but that will be hard to test without any sample data.

WITH CTE AS ( SELECT LetterID, [Status], MAX(CASE WHEN [Status] = 'Error' THEN 1 ELSE 0 END) OVER(ORDER BY DateTimeColumn ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS Error_Flag FROM YourTable )

SELECT
LetterID,
[Status]
FROM CTE
WHERE Error_Flag = 1

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 3

thank you
But using both the above methods i get rows only with "error" while i want rows above error and below error.

I can achieve this with cursor but do not wish to.

drew.allen SSC Guru Points: 76944 More actions · Answer 4

khushbu - Wednesday, February 6, 2019 12:20 AM
thank you
But using both the above methods i get rows only with "error" while i want rows above error and below error.
I can achieve this with cursor but do not wish to.

It does work. I just tested it. Notice that I'm filtering on the calculated Error_Flag rather than the table's status field.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Thom A SSC Guru Points: 99126 More actions · Answer 5

I admit, mine wouldn't have worked due to a missing comma, but if you fix that they do indeed both work (giving different results, depending on your need). You can try dbfiddle if you like.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 6

Here is one more way:

declare @t table (id char(1), cat char(5)) insert into @t (id, cat) select 'a', 'info' union select 'b', 'info' union select 'c', 'info' union select 'd', 'error' union select 'e', 'info' union select 'f', 'info';

with RowNums as ( select row_number() over (order by id) as RowNum, id, cat from @t), GetErrosRecord as ( SELECT RowNum FROM RowNums WHERE cat = 'error') select RowNums.id, RowNums.cat FROM RowNums INNER JOIN GetErrosRecord ON RowNums.RowNum >= GetErrosRecord.RowNum - 2 AND RowNums.RowNum <= GetErrosRecord.RowNum + 2

edited - For some reason didn't notice that the record with the value Error should also be included in the resultset. Removed from the query the criteria that filtered it out.

Adi

drew.allen SSC Guru Points: 76944 More actions · Answer 7

Thom A - Wednesday, February 6, 2019 8:16 AM
I admit, mine wouldn't have worked due to a missing comma, but if you fix that they do indeed both work (giving different results, depending on your need). You can try dbfiddle if you like.

dbfiddle looks cool. I may have to bookmark that for when I don't have access to SQL Server.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Thom A SSC Guru Points: 99126 More actions · Answer 8

drew.allen - Wednesday, February 6, 2019 9:40 AM
Thom A - Wednesday, February 6, 2019 8:16 AM
I admit, mine wouldn't have worked due to a missing comma, but if you fix that they do indeed both work (giving different results, depending on your need). You can try dbfiddle if you like.
dbfiddle looks cool. I may have to bookmark that for when I don't have access to SQL Server.
Drew

Stack Overflow introduced me to it awhile ago; it's been really useful for explaining things on the fly (especially when you don't have access to an instance like you said). I like that you can create batches in it as well, something that some other tools really lack.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk