1

Topic: Sampling successively going records

Good afternoon. Help please with writing of request for sampling successively going records (sorting according to date) that from a subgroup with identical ID it was selected minimum and date Trackdate is maximum
ID TrackDate CarName
1 2018-03-02 07:00:56.000 GAS 3309
1 2018-03-02 07:09:56.000 GAS 3309
1 2018-03-02 07:18:56.000 GAS 3309
1 2018-03-02 07:30:23.000 GAS 3309
3 2018-03-02 07:30:33.000 GAS 3309
3 2018-03-02 07:30:43.000 GAS 3309
3 2018-03-02 07:33:15.000 GAS 3309
3 2018-03-02 07:42:38.000 GAS 3309
2 2018-03-02 07:42:41.000 GAS 3309
2 2018-03-02 07:42:44.000 GAS 3309
2 2018-03-02 07:42:58.000 GAS 3309
2 2018-03-02 07:43:02.000 GAS 3309
3 2018-03-02 07:43:05.000 GAS 3309
3 2018-03-02 07:43:13.000 GAS 3309
3 2018-03-02 07:46:03.000 GAS 3309
1 2018-03-02 07:47:21.000 GAS 3309
1 2018-03-02 07:47:24.000 GAS 3309
1 2018-03-02 09:18:24.000 GAS 3309
3 2018-03-02 09:19:03.000 GAS 3309
3 2018-03-02 09:42:25.000 GAS 3309
3 2018-03-02 09:57:59.000 GAS 3309
3 2018-03-02 10:00:28.000 GAS 3309
3 2018-03-02 10:02:51.000 GAS 3309
1 2018-03-02 10:03:12.000 GAS 3309
1 2018-03-02 10:03:16.000 GAS 3309
1 2018-03-02 13:12:14.000 GAS 3309
1 2018-03-02 13:12:21.000 GAS 3309
The result of request should be such:
1 2018-03-02 07:00:56.000 GAS 3309
1 2018-03-02 07:30:23.000 GAS 3309
3 2018-03-02 07:30:33.000 GAS 3309
3 2018-03-02 07:42:38.000 GAS 3309
2 2018-03-02 07:42:41.000 GAS 3309
2 2018-03-02 07:43:02.000 GAS 3309
3 2018-03-02 07:43:05.000 GAS 3309
3 2018-03-02 07:46:03.000 GAS 3309
1 2018-03-02 07:47:21.000 GAS 3309
1 2018-03-02 09:18:24.000 GAS 3309
3 2018-03-02 09:19:03.000 GAS 3309
3 2018-03-02 10:02:51.000 GAS 3309
1 2018-03-02 10:03:12.000 GAS 3309
1 2018-03-02 13:12:21.000 GAS 3309

2

Re: Sampling successively going records

Search in a forum gives 2  possible decisions

3

Re: Sampling successively going records

row_number () over (partition by ID order by TrackDate asc) as rn1
row_number () over (partition by ID order by TrackDate desc) as rn2
To leave only records at which (rn1 or rn2) = 1

4

Re: Sampling successively going records

Records to renumber in the increasing and decreasing order. To leave where number is equal to unit.

5

Re: Sampling successively going records

The grandfather;
Does not approach, I already tried, it turns out here such:
rn1 rn2 ID TrackDate CarName
58 581 3 2018-03-02 07:42:16.000 GAS 3309
59 580 3 2018-03-02 07:42:21.000 GAS 3309
60 579 3 2018-03-02 07:42:38.000 GAS 3309
1 6 2 2018-03-02 07:42:41.000 GAS 3309
2 5 2 2018-03-02 07:42:44.000 GAS 3309
3 4 2 2018-03-02 07:42:58.000 GAS 3309
4 3 2 2018-03-02 07:43:02.000 GAS 3309
61 578 3 2018-03-02 07:43:05.000 GAS 3309
62 577 3 2018-03-02 07:43:13.000 GAS 3309
63 576 3 2018-03-02 07:43:25.000 GAS 3309

6

Re: Sampling successively going records

MAPODEP wrote:

Good afternoon. Help please with writing of request for sampling successively going records (sorting according to date) that from a subgroup with identical ID it was selected minimum and date Trackdate is maximum
ID TrackDate CarName
1 2018-03-02 07:00:56.000 GAS 3309
1 2018-03-02 07:09:56.000 GAS 3309
1 2018-03-02 07:18:56.000 GAS 3309
1 2018-03-02 07:30:23.000 GAS 3309
1 2018-03-02 07:47:21.000 GAS 3309
1 2018-03-02 07:47:24.000 GAS 3309
1 2018-03-02 09:18:24.000 GAS 3309
1 2018-03-02 10:03:12.000 GAS 3309
1 2018-03-02 10:03:16.000 GAS 3309
1 2018-03-02 13:12:14.000 GAS 3309
1 2018-03-02 13:12:21.000 GAS 3309
The result of request should be such:
1 2018-03-02 07:00:56.000 GAS 3309
1 2018-03-02 07:30:23.000 GAS 3309
1 2018-03-02 07:47:21.000 GAS 3309
1 2018-03-02 09:18:24.000 GAS 3309
1 2018-03-02 10:03:12.000 GAS 3309
1 2018-03-02 13:12:21.000 GAS 3309

I.e. - the order of records at you bluntly visual ?

7

Re: Sampling successively going records

Maxx;
on trackDate

8

Re: Sampling successively going records

MAPODEP;
That is sorting

9

Re: Sampling successively going records

MAPODEP wrote:

MAPODEP;
That is sorting

sorting that it is necessary to select the first and last record from the list (the grand-dad's approach)
If the version allows that it is possible to look on FISRT\LAST fisrt_value

10

Re: Sampling successively going records

Maxx wrote:

Search in a forum gives 2  possible decisions

http://www.sql.ru/forum/1222969/generac … irst_value

11

Re: Sampling successively going records

MAPODEP wrote:

does not approach, I already tried, it turns out here such:

top with ties

12

Re: Sampling successively going records

Maxx;
Understand - it is necessary for me that would select the maximum and minimum date on ID within repeating successively records, that is from this
ID TrackDate CarName
1 2018-03-02 07:00:56.000 GAS of 3309 mines
1 2018-03-02 07:09:56.000 GAS 3309
1 2018-03-02 07:18:56.000 GAS 3309
1 2018-03-02 07:30:23.000 GAS 3309
3 2018-03-02 07:30:33.000 GAS of 3309 mines
3 2018-03-02 07:30:43.000 GAS 3309
3 2018-03-02 07:33:15.000 GAS 3309
3 2018-03-02 07:42:38.000 GAS 3309
2 2018-03-02 07:42:41.000 GAS of 3309 mines
2 2018-03-02 07:42:44.000 GAS 3309
2 2018-03-02 07:42:58.000 GAS 3309
2 2018-03-02 07:43:02.000 GAS 3309
3 2018-03-02 07:43:05.000 GAS of 3309 mines
3 2018-03-02 07:43:13.000 GAS 3309
3 2018-03-02 07:46:03.000 GAS 3309
1 2018-03-02 07:47:21.000 GAS of 3309 mines
1 2018-03-02 07:47:24.000 GAS 3309
1 2018-03-02 09:18:24.000 GAS 3309
3 2018-03-02 09:19:03.000 GAS of 3309 mines
3 2018-03-02 09:42:25.000 GAS 3309
3 2018-03-02 09:57:59.000 GAS 3309
3 2018-03-02 10:00:28.000 GAS 3309
3 2018-03-02 10:02:51.000 GAS 3309
1 2018-03-02 10:03:12.000 GAS of 3309 mines
1 2018-03-02 10:03:16.000 GAS 3309
1 2018-03-02 13:12:14.000 GAS 3309
1 2018-03-02 13:12:21.000 GAS 3309
To receive it
ID TrackDate CarName
1 2018-03-02 07:00:56.000 GAS of 3309 mines
1 2018-03-02 07:30:23.000 GAS 3309
3 2018-03-02 07:30:33.000 GAS of 3309 mines
3 2018-03-02 07:42:38.000 GAS 3309
2 2018-03-02 07:42:41.000 GAS of 3309 mines
2 2018-03-02 07:43:02.000 GAS 3309
3 2018-03-02 07:43:05.000 GAS of 3309 mines
3 2018-03-02 07:46:03.000 GAS 3309
1 2018-03-02 07:47:21.000 GAS of 3309 mines
1 2018-03-02 09:18:24.000 GAS 3309
3 2018-03-02 09:19:03.000 GAS of 3309 mines
3 2018-03-02 10:02:51.000 GAS 3309
1 2018-03-02 10:03:12.000 GAS of 3309 mines
1 2018-03-02 13:12:21.000 GAS 3309

13

Re: Sampling successively going records

MAPODEP;
And we about the same.

14

Re: Sampling successively going records

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING if version 2012 and above

15

Re: Sampling successively going records

Maxx;
MSSQL 9.0.5000 (

16

Re: Sampling successively going records

Wlr-l;
So it considers not in a subgroup, and on all records
ID TrackDate CarName
1 2018-03-02 07:00:56.000 GAS of 3309 mines
3 2018-03-02 07:30:33.000 GAS of 3309 mines
2 2018-03-02 07:42:41.000 GAS of 3309 mines
2 2018-03-02 07:43:02.000 GAS 3309
3 2018-03-02 10:02:51.000 GAS 3309
1 2018-03-02 13:12:21.000 GAS 3309

17

Re: Sampling successively going records

MAPODEP;
You to yourselves created a problem, it was necessary to number groups at once. And now they exist only in imagination.
Try to enumerate the cursor with sorting according to date in which body catch changes ID.

18

Re: Sampling successively going records

MAPODEP;
The grandfather wrote all: grouping on ID, sorting on TrackDate in two directions, a filtration of the necessary records.
What here can be not so?

19

Re: Sampling successively going records

Wlr-l;
Taking into account the remark Vladislav Kolosov.

20

Re: Sampling successively going records

Wlr-l;
What I not so then do?

;with Table1 as (
select
row_number () over (partition by ID order by TrackDate asc) as rn1;
row_number () over (partition by ID order by TrackDate desc) as rn2, *
from trackhistorytmp
)
select *
from Table1 t
where t.rn1=1 or t.rn2=1
order by trackdate

21

Re: Sampling successively going records

MAPODEP
Error right at the beginning as Vladislav Kolosov noted. Eliminate it then the remaining will be correct.

22

Re: Sampling successively going records

Wlr-l;
If I knew as to enumerate the cursor, I already would make it)

23

Re: Sampling successively going records

MAPODEP wrote:

that I not so then do?

declare @t table (ID int, TrackDate datetime, CarName varchar (10))
insert @t
(ID, TrackDate, CarName)
values
(1, ' 2018-03-02 07:00:56.000 ', ' 3309 ');
(1, ' 2018-03-02 07:09:56.000 ', ' 3309 ');
(1, ' 2018-03-02 07:18:56.000 ', ' 3309 ');
(1, ' 2018-03-02 07:30:23.000 ', ' 3309 ');
(3, ' 2018-03-02 07:30:33.000 ', ' 3309 ');
(3, ' 2018-03-02 07:30:43.000 ', ' 3309 ');
(3, ' 2018-03-02 07:33:15.000 ', ' 3309 ');
(3, ' 2018-03-02 07:42:38.000 ', ' 3309 ');
(2, ' 2018-03-02 07:42:41.000 ', ' 3309 ');
(2, ' 2018-03-02 07:42:44.000 ', ' 3309 ');
(2, ' 2018-03-02 07:42:58.000 ', ' 3309 ');
(2, ' 2018-03-02 07:43:02.000 ', ' 3309 ');
(3, ' 2018-03-02 07:43:05.000 ', ' 3309 ');
(3, ' 2018-03-02 07:43:13.000 ', ' 3309 ');
(3, ' 2018-03-02 07:46:03.000 ', ' 3309 ')
;with tt as (
select
row_number () over (partition by ID order by TrackDate asc) as rn1;
row_number () over (partition by ID order by TrackDate desc) as rn2, *
from @t
)
select
ID, TrackDate, CarName
from
tt
where
rn1=1 or rn2=1
order by
ID, TrackDate
1 2018-03-02 07:00:56.000 3309
1 2018-03-02 07:30:23.000 3309
2 2018-03-02 07:42:41.000 3309
2 2018-03-02 07:43:02.000 3309
3 2018-03-02 07:30:33.000 3309
3 2018-03-02 07:46:03.000 3309

That result what is necessary?

24

Re: Sampling successively going records

And, understood about what you groups smile

25

Re: Sampling successively going records

MAPODEP;

declare @t table (ID int, TrackDate datetime, CarName varchar (30));
insert into @t
select 1, ' 20180302 07:00:56.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 07:09:56.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 07:18:56.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 07:30:23.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 07:30:33.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 07:30:43.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 07:33:15.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 07:42:38.000 ', ' GAS 3309 ' union all
select 2, ' 20180302 07:42:41.000 ', ' GAS 3309 ' union all
select 2, ' 20180302 07:42:44.000 ', ' GAS 3309 ' union all
select 2, ' 20180302 07:42:58.000 ', ' GAS 3309 ' union all
select 2, ' 20180302 07:43:02.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 07:43:05.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 07:43:13.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 07:46:03.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 07:47:21.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 07:47:24.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 09:18:24.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 09:19:03.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 09:42:25.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 09:57:59.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 10:00:28.000 ', ' GAS 3309 ' union all
select 3, ' 20180302 10:02:51.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 10:03:12.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 10:03:16.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 13:12:14.000 ', ' GAS 3309 ' union all
select 1, ' 20180302 13:12:21.000 ', ' GAS 3309 ';
with t as
(
select
ID, CarName, TrackDate;
row_number () over (order by TrackDate) - row_number () over (partition by ID order by TrackDate) as g
from
@t
)
select
a. ID, a. CarName, b.dt
from
(select g, ID, CarName, min (TrackDate), max (TrackDate) from t group by ID, CarName, g) a (g, ID, CarName, min_dt, max_dt) cross apply
(select a.min_dt union all select a.max_dt) b (dt)
order by
a.g, a. ID, b.dt;