removing unwanted rows from an sql server ranked table

it will help to copy and paste both source and result data for us to build temp tables to try the our solutions prior to posting them. Also post the sql you already have.

Hi Mike,
code to get output

with t1 as
(
select
      t.acc1
    , t.TranDate
    , t.rate
    , case when t.TranDate = D.tranDate then 'Base'
           when t.rate > d.rate then 'Increase'
           when t.rate < d.rate then 'Decrease'
           else 'Equal'
      end as Movement
from YourTable t
inner join (
        select acc1, TranDate, rate, row_number() over(partition by acc1 order by TranDate ASC) as rn
        from YourTable
        ) d ON t.acc1 = d.acc1 and d.rn=1
where (
        t.TranDate = d.TranDate
      or 
        t.rate < d.rate
      )
      ) ,
	  /* t2 gets the ist rate change from t1*/

t2 as
(
select 
acc1,trandate,rate,movement,
rank() over (partition by acc1 order by trandate asc)rnk
from t1)
select rnk as Rank_,acc1,trandate,rate,movement from t2
order by  2,3;

Select allOpen in new window

yourtable contents
table-contents.xlsx

What is your SQL Server version?

2016  developer edition on my laptop

Thanks Mike for providing sample data.
blossompark, you can try lag.

;with cte as (
Select *,lag(rate) over (partition by acc1 order by Tran_order) prev_rate,
       count(*) over (partition by acc1) cnt
  from @temp)
select Tran_Order, acc1, [Tran Date], rate 
  from cte 
 where (rate <> prev_rate or prev_rate is null)
/*
  	Tran_Order	acc1	Tran Date	rate
1	4	11188368	27.04.2007 00:00:00	1,35
2	6	11188368	30.04.2008 00:00:00	0,95
3	6	12863288	27.04.2007 00:00:00	1,35
4	8	12863288	16.05.2008 00:00:00	0,95
5	3	12978348	27.04.2007 00:00:00	1,35
6	4	12978348	03.05.2007 00:00:00	0,80
7	3	14878928	08.12.2006 00:00:00	1,35
8	5	14878928	13.12.2007 00:00:00	0,80
9	2	98743553	30.03.2007 00:00:00	0,90
10	3	98743553	10.05.2007 00:00:00	0,75
11	1	98771726	30.04.2007 00:00:00	0,75
12	1	98784330	25.04.2007 00:00:00	1,10
13	2	98784330	21.05.2008 00:00:00	1,00
14	1	98790248	03.05.2007 00:00:00	1,25
15	2	98790248	21.06.2007 00:00:00	1,10
16	3	98790248	21.11.2007 00:00:00	0,80

*/

Select allOpen in new window

thanks mike for the data!

Hi Huseyin,
thats almost perfect thanks!
for example, looking at the acc1 98790248 returned values...the values dated  2007/05/03 and 2007/06/21 are correct.
the third value should be dated 2007/11/21, i need the first dated change if that makes sense.....thank you

Hi Sharath, will try this solution now thanks.....ultomately i will be using teradata for this query, i should have explained that, sorry....not sure if teradata has the lag function but it might.....i am using sql server 2016 because that is what i have on my personal laptop.....

I want to thank all you guys.....i have struggled with this issue all weekend.... thank you all

Hi Sharath, almost perfect also! Dates are fine, only issue is that it contains acc1 98771726,  i need rows that have only 0ne value of acc1 removed....thanks!!

;with cte as (
Select *,lag(rate) over (partition by acc1 order by Tran_order) prev_rate,
       count(*) over (partition by acc1) cnt
  from @temp)
select Tran_Order, acc1, [Tran Date], rate 
  from cte 
 where (rate <> prev_rate or prev_rate is null)
   and cnt > 1
 order by acc1, Tran_order;

Select allOpen in new window

Hi Sharath, Huseyin and Mike,
I just want to thank you guys.... i was under a lot of stress with this issue and now the world feels a bit brighter...thank