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

3 Answers

1
Gordon Linoff On Best Solutions

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.

1
fa06 On

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
2
Salman A On

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