CODEWITHSUNDEEP
sqlsql-servert-sql
Hossein
Hossein

Reputation: 3113

How filter previous record if one field is same in sql

I have records like below:

ID            Date              Title        User 
1   2019-04-29 14:15:55.567      A           1222
2   2019-04-29 14:25:13.530      A           1222
3   2019-04-29 15:17:07.210      A           1222
4   2019-04-29 16:05:49.067      B           1048
5   2019-04-29 16:36:37.330      C           681
6   2019-04-29 16:37:16.250      C           681
7   2019-04-29 16:37:49.160      D           681
8   2019-04-29 16:38:48.803      C           681

I want to check each record with previous record and if Title of both is same , ignore previous.

I want see result like this:

ID            Date              Title        User 
3   2019-04-29 15:17:07.210      A           1222
4   2019-04-29 16:05:49.067      B           1048
6   2019-04-29 16:37:16.250      C           681
7   2019-04-29 16:37:49.160      D           681
8   2019-04-29 16:38:48.803      C           681

Upvotes: 3

Views: 541

Answers (3)

Fahmi
Fahmi

Reputation: 37473

use lag()

DEMO

select * from
(
select *, lag(title) over(order by date desc) as prevtitle
from tablename
)A where prevtitle is null or title<>prevtitle

OUTPUT:

id  dateval             title   userid
3   29/04/2019 15:17:07   A      1222
4   29/04/2019 16:05:49   B      1048
6   29/04/2019 16:37:16   C      681
7   29/04/2019 16:37:49   D      681
8   29/04/2019 16:38:48   C      681

Upvotes: 3

Salman Arshad
Salman Arshad

Reputation: 272106

You can use LAG function for this:

SELECT *
FROM (
    SELECT *
         , LAG(title) OVER (ORDER BY Date) AS prev_title
    FROM t
) AS x
WHERE title <> prev_title

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Use lead():

select t.*
from (select t.*,
             lead(title) over (order by id) as next_title
      from t
     ) t
where next_title is null or next_title <> title;

You want to use lead() because you want the most recent value in each group. You would use lag() if you wanted the earliest value.

I'm not sure if the ordering should be by id or the date column. For your sample data, they are equivalent.

Upvotes: 3

Related Questions