SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

    Create table InvoiceItems 
(
    ID int,
    SegmentID int,
    Created dateTime,
    [Type] int,
    InvoiceItemID int
)
INSERT INTO InvoiceItems values (1002330   , 1000322,  '2018-05-29 16:06:31.257',  1,  NULL)
INSERT INTO InvoiceItems values (1002331   , 1000322,  '2018-05-29 16:06:31.257',  2,  NULL)
INSERT INTO InvoiceItems values (1002332   , 1000322,  '2018-05-29 16:06:31.257',  3,  NULL)
INSERT INTO InvoiceItems values (1002333   , 1000322,  '2018-05-29 16:06:31.257',  4,  344)
INSERT INTO InvoiceItems values (1002334   , 1000322,  '2018-05-29 16:06:31.257',  5,  NULL)
INSERT INTO InvoiceItems values (1002335   , 1000322,  '2018-05-29 16:06:31.257',  6,  NULL)
INSERT INTO InvoiceItems values (1002370   , 1000322,  '2018-05-30 15:33:48.453',  2,  NULL)
INSERT INTO InvoiceItems values (1002371   , 1000322,  '2018-05-30 15:33:48.453',  3,  NULL)
INSERT INTO InvoiceItems values (1002604   , 1000322,  '2018-06-04 10:25:56.603',  3,  NULL)
INSERT INTO InvoiceItems values (1002605   , 1000322,  '2018-06-04 10:25:56.603',  4,  344)
INSERT INTO InvoiceItems values (1002685   , 1000369,  '2018-06-05 10:45:53.570',  1,  NULL)
INSERT INTO InvoiceItems values (1002686   , 1000369,  '2018-06-05 10:45:53.570',  2,  NULL)
INSERT INTO InvoiceItems values (1002687   , 1000369,  '2018-06-05 10:45:53.570',  3,  NULL)
INSERT INTO InvoiceItems values (1002688   , 1000369,  '2018-06-05 10:45:53.570',  4,  274)
INSERT INTO InvoiceItems values (1002689   , 1000369,  '2018-06-05 10:45:53.570',  5,  NULL)
INSERT INTO InvoiceItems values (1002690   , 1000369,  '2018-06-05 10:45:53.570',  6,  NULL)
INSERT INTO InvoiceItems values (1002691   , 1000369,  '2018-06-05 12:26:52.153',  3,  NULL)
INSERT INTO InvoiceItems values (1002692   , 1000369,  '2018-06-05 12:26:52.153',  4,  275)
INSERT INTO InvoiceItems values (1002761   , 1000369,  '2018-06-06 11:19:11.613',  2,  NULL)
INSERT INTO InvoiceItems values (1002762   , 1000369,  '2018-06-06 11:19:11.613',  3,  NULL)
INSERT INTO InvoiceItems values (1002763   , 1000369,  '2018-06-06 11:19:18.503',  2,  NULL)
INSERT INTO InvoiceItems values (1002764   , 1000369,  '2018-06-06 11:19:18.503',  3,  NULL)
INSERT INTO InvoiceItems values (1002765   , 1000369,  '2018-06-06 11:20:10.333',  2,  NULL)
INSERT INTO InvoiceItems values (1002766   , 1000369,  '2018-06-06 11:20:10.333',  3,  NULL)
INSERT INTO InvoiceItems values (1002767   , 1000369,  '2018-06-06 11:20:10.333',  4,  276)
INSERT INTO InvoiceItems values (1002768   , 1000369,  '2018-06-06 11:20:32.677',  2,  NULL)
INSERT INTO InvoiceItems values (1002769   , 1000369,  '2018-06-06 11:20:32.677',  3,  NULL)
27 rows affected

select * from InvoiceItems


ID
SegmentID
Created
Type
InvoiceItemID


1002330
1000322
29/05/2018 16:06:31
1
null

1002331
1000322
29/05/2018 16:06:31
2
null

1002332
1000322
29/05/2018 16:06:31
3
null

1002333
1000322
29/05/2018 16:06:31
4
344

1002334
1000322
29/05/2018 16:06:31
5
null

1002335
1000322
29/05/2018 16:06:31
6
null

1002370
1000322
30/05/2018 15:33:48
2
null

1002371
1000322
30/05/2018 15:33:48
3
null

1002604
1000322
04/06/2018 10:25:56
3
null

1002605
1000322
04/06/2018 10:25:56
4
344

1002685
1000369
05/06/2018 10:45:53
1
null

1002686
1000369
05/06/2018 10:45:53
2
null

1002687
1000369
05/06/2018 10:45:53
3
null

1002688
1000369
05/06/2018 10:45:53
4
274

1002689
1000369
05/06/2018 10:45:53
5
null

1002690
1000369
05/06/2018 10:45:53
6
null

1002691
1000369
05/06/2018 12:26:52
3
null

1002692
1000369
05/06/2018 12:26:52
4
275

1002761
1000369
06/06/2018 11:19:11
2
null

1002762
1000369
06/06/2018 11:19:11
3
null

1002763
1000369
06/06/2018 11:19:18
2
null

1002764
1000369
06/06/2018 11:19:18
3
null

1002765
1000369
06/06/2018 11:20:10
2
null

1002766
1000369
06/06/2018 11:20:10
3
null

1002767
1000369
06/06/2018 11:20:10
4
276

1002768
1000369
06/06/2018 11:20:32
2
null

1002769
1000369
06/06/2018 11:20:32
3
null


select ii.*, max(InvoiceItemID) over (partition by grp)
from (select ii.*,
             count(InvoiceItemID) over (order by created desc) as grp
      from InvoiceItems ii
     ) ii
order by created


ID
SegmentID
Created
Type
InvoiceItemID
grp
(No column name)


1002330
1000322
29/05/2018 16:06:31
1
null
5
344

1002331
1000322
29/05/2018 16:06:31
2
null
5
344

1002332
1000322
29/05/2018 16:06:31
3
null
5
344

1002333
1000322
29/05/2018 16:06:31
4
344
5
344

1002334
1000322
29/05/2018 16:06:31
5
null
5
344

1002335
1000322
29/05/2018 16:06:31
6
null
5
344

1002370
1000322
30/05/2018 15:33:48
2
null
4
344

1002371
1000322
30/05/2018 15:33:48
3
null
4
344

1002604
1000322
04/06/2018 10:25:56
3
null
4
344

1002605
1000322
04/06/2018 10:25:56
4
344
4
344

1002685
1000369
05/06/2018 10:45:53
1
null
3
274

1002686
1000369
05/06/2018 10:45:53
2
null
3
274

1002687
1000369
05/06/2018 10:45:53
3
null
3
274

1002688
1000369
05/06/2018 10:45:53
4
274
3
274

1002689
1000369
05/06/2018 10:45:53
5
null
3
274

1002690
1000369
05/06/2018 10:45:53
6
null
3
274

1002691
1000369
05/06/2018 12:26:52
3
null
2
275

1002692
1000369
05/06/2018 12:26:52
4
275
2
275

1002761
1000369
06/06/2018 11:19:11
2
null
1
276

1002762
1000369
06/06/2018 11:19:11
3
null
1
276

1002763
1000369
06/06/2018 11:19:18
2
null
1
276

1002764
1000369
06/06/2018 11:19:18
3
null
1
276

1002765
1000369
06/06/2018 11:20:10
2
null
1
276

1002766
1000369
06/06/2018 11:20:10
3
null
1
276

1002767
1000369
06/06/2018 11:20:10
4
276
1
276

1002768
1000369
06/06/2018 11:20:32
2
null
0
null

1002769
1000369
06/06/2018 11:20:32
3
null
0
null


Warning: Null value is eliminated by an aggregate or other SET operation.

ID	SegmentID	Created	Type	InvoiceItemID
1002330	1000322	29/05/2018 16:06:31	1	null
1002331	1000322	29/05/2018 16:06:31	2	null
1002332	1000322	29/05/2018 16:06:31	3	null
1002333	1000322	29/05/2018 16:06:31	4	344
1002334	1000322	29/05/2018 16:06:31	5	null
1002335	1000322	29/05/2018 16:06:31	6	null
1002370	1000322	30/05/2018 15:33:48	2	null
1002371	1000322	30/05/2018 15:33:48	3	null
1002604	1000322	04/06/2018 10:25:56	3	null
1002605	1000322	04/06/2018 10:25:56	4	344
1002685	1000369	05/06/2018 10:45:53	1	null
1002686	1000369	05/06/2018 10:45:53	2	null
1002687	1000369	05/06/2018 10:45:53	3	null
1002688	1000369	05/06/2018 10:45:53	4	274
1002689	1000369	05/06/2018 10:45:53	5	null
1002690	1000369	05/06/2018 10:45:53	6	null
1002691	1000369	05/06/2018 12:26:52	3	null
1002692	1000369	05/06/2018 12:26:52	4	275
1002761	1000369	06/06/2018 11:19:11	2	null
1002762	1000369	06/06/2018 11:19:11	3	null
1002763	1000369	06/06/2018 11:19:18	2	null
1002764	1000369	06/06/2018 11:19:18	3	null
1002765	1000369	06/06/2018 11:20:10	2	null
1002766	1000369	06/06/2018 11:20:10	3	null
1002767	1000369	06/06/2018 11:20:10	4	276
1002768	1000369	06/06/2018 11:20:32	2	null
1002769	1000369	06/06/2018 11:20:32	3	null