;with cte as (
select ID - row_number() over (partition by initial order by ID) rn ,*
from HistoryChanges),
cte1 as (
select *,rank() over (partition by initial order by rn desc) rn1
from cte)
select ID, initial, DateEntered
from cte1
where rn1 = 1
order by id
declare @HistoryChanges table (ID int identity, initial varchar(5), DateEntered datetime)
insert @HistoryChanges values ('A', '2016-08-30 10:00:00')
insert @HistoryChanges values ('A', '2016-08-30 10:01:00')
insert @HistoryChanges values ('A', '2016-08-30 10:02:00')
insert @HistoryChanges values ('B', '2016-08-30 10:03:00')
insert @HistoryChanges values ('B', '2016-08-30 10:04:00')
insert @HistoryChanges values ('A', '2016-08-30 10:05:00')
insert @HistoryChanges values ('A', '2016-08-30 10:06:00')
;with cte as (
select ID - row_number() over (partition by initial order by ID) rn ,*
from @HistoryChanges),
cte1 as (
select *,rank() over (partition by initial order by rn desc) rn1
from cte)
select ID, initial, DateEntered
from cte1
where rn1 = 1
order by id
/*
ID initial DateEntered
4 B 2016-08-30 10:03:00.000
5 B 2016-08-30 10:04:00.000
6 A 2016-08-30 10:05:00.000
7 A 2016-08-30 10:06:00.000
*/
declare @initial varchar(5)
select @initial = 'A'
;with cte as (
select ID - row_number() over (partition by initial order by ID) rn ,*
from HistoryChanges
where initial = @initial),
cte1 as (
select *,rank() over (partition by initial order by rn desc) rn1
from cte)
select ID, initial, DateEntered
from cte1
where rn1 = 1
order by id
Open in new window
should work okay.
Good luck!