SQL: retrieve only the records whose value has changed with first row

Question

Sorry, newbie here.

Similar to this here: SQL: retrieve only the records whose value has changed

I wanted to find out how to also include the very first row? The rows with the *'s?

ID  description    eaudittimestamp
--  -----------    -----------------------
777012  above       2017-03-27 10:09:59.330 *
777012  above       2017-03-27 10:09:58.550
777012  below       2017-03-27 10:26:03.560 *
777012  below       2017-03-27 10:36:02.423
777012  below       2017-03-27 10:37:15.250
777012  middle      2017-03-27 10:49:11.680 *
777012  middle      2017-03-27 10:49:18.870
777013      something       2017-03-27 11:49:18.870
777013      something       2017-03-27 12:49:18.870
777014      nodescription   2017-03-27 12:49:18.870

Apologies, I new to this. let me rephrase. I have a table, which has ID, description and timestamp. Similar to an audit table. How do I to find the rows (including the first) where the description has changed.

And how to exclude the ones that only have 1 row? i.e. 777013 and 777014?

you use like for the rate and get the max or min based on date or just do a top 1. — RoMEoMusTDiE, Mar 27 '17 at 23:45

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

with ct as
(
    select Code, Date, Rate,
           row_number() over (partition by Code, Rate order by Code, Date) as rn
    from your_table
)
select Code, Date, Rate
from   ct
where  rn = 1
order by Code, Date;

Update

declare @foo table(ID int,  description varchar(100),  eaudittimestamp datetime);
insert into @foo values
(777012, 'above', '2017-03-27 10:09:59.330'),
(777012, 'above', '2017-03-27 10:09:58.550'),
(777012, 'below', '2017-03-27 10:26:03.560'),
(777012, 'below', '2017-03-27 10:36:02.423'),
(777012, 'below', '2017-03-27 10:37:15.250'),
(777012, 'middle', '2017-03-27 10:49:11.680'),
(777012, 'middle', '2017-03-27 10:49:18.870'),
(777013, 'something', '2017-03-27 11:49:18.870'),
(777013, 'something', '2017-03-27 12:49:18.870'),
(777014, 'nodescription', '2017-03-27 12:49:18.870');

with ct as
(
    select ID, description, eaudittimestamp,
           row_number() over (partition by ID, description order by eaudittimestamp) rn,
           count(*) over (partition by ID) as ct
    from   @foo
)
select ID, Description, eaudittimestamp
from ct
where rn = 1 and ct > 1;

GO

    ID | Description | eaudittimestamp    
-----: | :---------- | :------------------
777012 | above       | 27/03/2017 10:09:58
777012 | below       | 27/03/2017 10:26:03
777012 | middle      | 27/03/2017 10:49:11
777013 | something   | 27/03/2017 11:49:18

dbfiddle here

Gordon Linoff · Answer 2 · 2017-03-28T11:42:54.747

1

For the data you have provided, row_number() works:

select t.*
from (select t.*,
             row_number() over (partition by id, description order by eaudittimestamp) as seqnum
      from t
     ) t
where seqnum = 1;

This returns one row per id/description pair. The one with the lowest eaudittimestamp. That appears to be what you want.

EDIT:

If you want to exclude the groups with one row, just use another window function:

select t.*
from (select t.*,
             row_number() over (partition by id, description order by eaudittimestamp) as seqnum,
             min(description) over (partition by id) as min_d,
             max(description) over (partition by id) as max_d
      from t
     ) t
where seqnum = 1 and min_d <> max_d;

edited Mar 28 '17 at 11:42

answered Mar 28 '17 at 00:00

Gordon Linoff

1,242,037
58
646
786

thank you Gordon. what if there is multiples? I,e it should have returned 3 rows? – Kay_e_n Mar 28 '17 at 00:06
@Kayen this is the part where you look up [`row_number()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql) and start trying to understand it for yourself. Go play on [SQL Fiddle](http://sqlfiddle.com); I've created a [demo](http://sqlfiddle.com/#!3/b7c80/3) for you. – canon Mar 28 '17 at 00:10
this works, but returns all rows where seq =1. what if we only want to find the rows where there are changes?. – Kay_e_n Mar 28 '17 at 00:39
sorry not so clear. I fix example. with Gordon's code, 777013 would appear as it would be seq = 1. how to make not include? – Kay_e_n Mar 28 '17 at 02:35
@Kay_e_n . . . One method would be `count(distinct) over`, but I don't think that works in SQL Server 2008. You can just look at the minimum and maximum values of `description` for each `id` and be sure that they are different. – Gordon Linoff Mar 28 '17 at 11:43

SQL: retrieve only the records whose value has changed with first row

2 Answers2