CODEWITHSUNDEEP
sqlsql-server
user716255
user716255

Reputation: 443

How to compare two adjacent rows in SQL?

In SQL is there a way to compare two adjacent rows. In other words if C2 = BEM and C3 = Compliance or if C4 = Compliance and C5 = BEM, then return true. But consecutive rows are identical like in C6 = BEM and C7 = BEM, then return fail.

enter image description here

Upvotes: 0

Views: 1231

Answers (3)

Daniel Gimenez
Daniel Gimenez

Reputation: 20494

Assuming you have some sort of column that you can use to determine the row order then you can use the LEAD window function to get the next value.

SELECT 
  [A],
  CASE 
    WHEN [A] = LEAD([A], 1, [A]) OVER (ORDER BY SomeSortIndex) THEN 'Fail' 
    ELSE 'Ok' 
  END [B]
FROM src

The additional parameters in the LEAD function specify the row offset and default value in case there is no additional row. By using the current value as the default it will cause the condition to be true and display Fail like the last result in your example.

Upvotes: 1

GMB
GMB

Reputation: 222462

As a starter: a SQL table represents an unordered set of rows; there is no inherent ordering. Assuming that you have a column that defines the ordering of the rows, say id, and that your values are stored in column col, you can use lead() and a case expression as follows:

select col,
    case when col = lead(col, 1, col) over(order by id) 
        then 'Fail' else 'OK' 
    end as status
from mytable t

Upvotes: 1

Sander
Sander

Reputation: 4042

Check out the lead() and lag() functions.

They do work best (most reliable) with a sorting field... Your sample does not appear to contain such a field. I added a sorting field in my second solution.

The coalesce() function handles the first row that does not have a preceeding row.

Solution 1 without sort field

create table data
(
  A nvarchar(10)
);

insert into data (A) values
('BEM'),
('Compliance'),
('BEM'),
('Compliance'),
('BEM'),
('Compliance'),
('Compliance'),
('Compliance');

select d.A,
       coalesce(lag(d.A) over(order by (select null)), '') as lag_A,
       case
         when d.A <> coalesce(lag(d.A) over(order by (select null)), '')
         then 'Ok'
         else 'Fail'
       end as B
from data d;

Solution 2 with sort field

create table data2
(
  Sort int,
  A nvarchar(10)
);

insert into data2 (Sort, A) values
(1, 'BEM'),
(2, 'Compliance'),
(3, 'BEM'),
(4, 'Compliance'),
(5, 'BEM'),
(6, 'Compliance'),
(7, 'Compliance'),
(8, 'Compliance');

select d.A,
       case
         when d.A <> coalesce(lag(d.A) over(order by d.Sort), '')
         then 'Ok'
         else 'Fail'
       end as B
from data2 d
order by d.Sort;

Fiddle with results.

Upvotes: 1

Related Questions