CODEWITHSUNDEEP
sqlsql-servert-sql
Vikram Rathaur
Vikram Rathaur

Reputation: 11

How to compare two consecutive rows values in SQL

I have data related to Employees Branch change log in one of the table as following.

UniqueID  UID  Branch_Id Created_On
   18791  173  31  2-Jul-2020  
   18411  173  31  15-May-2020 
   17867  173  31  23-Mar-2020 
   14614  173  27  25-Jul-2019 
   12958  173  27  11-May-2019 
   11783  173  27  7-Mar-2019  
   11780  173  27  7-Mar-2019  
   9719   173  27  14-Nov-2018 
   9304   173  27  18-Oct-2018 
   9103   173  27  9-Oct-2018  
   7958   173  27  17-Sep-2018 
   4549   173  27  15-Mar-2018 
   3272   173  27  9-Jan-2018  
   2844   173  20  6-Dec-2017  
   2481   173  20  8-Nov-2017  
   58     173  27  4-Jul-2013

I need to display the above data as following.

UniqueId  UID    Branch_Id  Created_On     
  17867   173     31        23-Mar-2020      
  3272    173     27        9-Jan-2018        
  2481    173     20        8-Nov-2017       
  58      173     27        4-Jul-2013

What I have tried

SELECT *
FROM LogTable
INNER JOIN (
    SELECT Min(UniqueId) as MinUniqueId
    FROM LogTable
    GROUP BY Branch_Id
) AS Filtered ON LogTable.UniqueId = Filtered.MinUniqueId

Please guide me how can I achieve this?

Thank you.

Upvotes: 1

Views: 2682

Answers (2)

Andrei Odegov
Andrei Odegov

Reputation: 3429

I suggest the following query:

WITH
  a AS (
    SELECT *,
      ROW_NUMBER() OVER(ORDER BY Created_on)-
      ROW_NUMBER() OVER(PARTITION BY Branch_Id ORDER BY Created_on) as grp
    FROM LogTable
  ),
  b AS (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY Branch_Id, grp ORDER BY Created_on) as rn
    FROM a
  )
SELECT UniqueId, UID, Branch_Id, Created_On
FROM b
WHERE rn = 1;

Result:

+----------+-----+-----------+------------+
| UniqueId | UID | Branch_Id | Created_On |
+----------+-----+-----------+------------+
|     2481 | 173 |        20 | 2017-11-08 |
|       58 | 173 |        27 | 2013-07-04 |
|     3272 | 173 |        27 | 2018-01-09 |
|    17867 | 173 |        31 | 2020-03-23 |
+----------+-----+-----------+------------+

db<>fiddle

A more concise version:

WITH
  a AS (
    SELECT *,
      LAG(Branch_Id) OVER(ORDER BY Created_on) Prev_Branch_Id
    FROM LogTable
  )
SELECT UniqueID, UID, Branch_Id, Created_On
FROM a
WHERE
  Prev_Branch_Id IS NULL OR
  Branch_ID != Prev_Branch_Id;

db<>fiddle

Upvotes: 4

Serg
Serg

Reputation: 22811

Flag every Branch_Id change and select flagged items

SELECT UniqueId, UID, Branch_Id, Created_On
FROM (
    SELECT *,
       case lag(branch_id) over(partition by uid order by created_on) when branch_id then 0 else 1 end flag
    FROM LogTable  
  ) t
WHERE flag = 1
ORDER BY Created_On DESC;

Upvotes: 6

Related Questions