create table #duplicaterows
(

id int,
name varchar(50)

)
go

insert into #duplicaterows
select 200,'anurag'
union all select 200,'anurag'
union all select 200,'anurag'
union all select 100,'abhi'
union all select 100,'abhi'
union all select 250,'mukesh'
union all select 250,'mukesh'
go

select * from #duplicaterows












-->how to get the distinct rows from table

-------------
select *
from #duplicaterows
-------------

;with cte(row,id,name)
as
(
select row_number() over(order by id) as row,*  from #duplicaterows
)
select * from cte c1
where c1.row =
(
select max(c2.row) from cte c2 
where c2.id=c1.id and c1.name=c2.name
)

O/P:







the best way to understand the above query is
1)first thing we don't have identity column. Use cte and create row number for the table.
2)Then correlated query. [imagine both tables as below]. Now both tables are created as c1 and c2
3)now we will selecting max row from cte(c1) [comparing with cte(c2) fetching maximum one] 


--cte c1












--Fetching 2,5,7 (the max one)

so output is :


------------Trick 2: ------------------------------------
;with cte(row,id,name)
as
(
select row_number() over(partition by id order by id) as row,*  from #duplicaterows
)
select * from cte c1
where c1.row =
(
select max(c2.row) from cte c2 
where c2.id=c1.id and c1.name=c2.name
)

o/p









Lets understand this








2)selecting the max row individually for the matching id using corrleated query(the inner query).

where c1.row =
(
select max(c2.row) from cte c2 
where c2.id=c1.id and c1.name=c2.name
)

-----------------------------------------------------Deleting----------------------------------------------------
Trick 1:

So the main table is :








;with cte(row,id,name)
as
(
select row_number() over(order by id) as row,*  from #duplicaterows
)
select * from cte c1


So if we can delete the the id -->2, id-->(3,4), id (6). Then we can delete the duplicate rows. 
In the inner query where we get the max. So if outer query is less than that max ==> delete. 
Means for example

: : three anurag is there.

Delete anurag from the base table 
(outer query) whose id is < (inner query) the max id of anurag[5].

so 3 and 4 will be deleted.

The final query will be like this :

;with cte(row,id,name)
as
(
select row_number() over(order by id) as row,*  from #duplicaterows
)
delete from cte 
where row <
(
select max(c2.row) from cte c2 
where c2.id= cte.id and cte.name=c2.name
)

select  * from #duplicaterows









Trick 2:

;with cte(row,id,name)
as
(
select row_number() over(partition by id order by id) as row,*  from #duplicaterows
)

delete from cte where row >1 

--Kindly give feedback if it helped you.

select unicode('A')--65
select unicode('a')--97
select unicode('Abfdgdfg')--65
select unicode('12')--49


select ascii('A')--65
select ascii('a')--97
select ascii('Abfdgdfg')--65
select ascii('12')--49

select substring('anurag',3,3)--ura
select UNICODE(SUBSTRING('anurag', 3, 3));--117
select ascii(SUBSTRING('anurag', 3, 3))--117




 SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');--8
 SELECT PATINDEX('%l%', 'please ensure the door is locked');--2
 SELECT PATINDEX('%do%', 'please ensure the door is locked');--19

create table #quotetable
(

 name varchar(100)

)

insert into #quotetable
select 'Anurag'

select quotename(name) from #quotetable-----[Anurag]
select quotename(name,'<') from #quotetable--<Anurag>
select quotename(name,'(') from #quotetable--(Anurag)
select quotename(name,'"') from #quotetable--"Anurag"

create table cummulativetable
(

salary int

)

insert into cummulativetable values(100)
insert into cummulativetable values(200)
insert into cummulativetable values(300)
insert into cummulativetable values(400)
insert into cummulativetable values(500)

select * from cummulativetable













----------------------------USING CTE-------------------------------
;with cte(rownumber,salary)
as
(
select row_number() over( order by salary) as rownumber,salary
from cummulativetable
)
select rownumber,salary,
(
select sum(salary)
from 
cte c1
where c1.rownumber <= c2.rownumber
) 
from cte c2



Trick 2 :


;with cte(rownumber,salary)
as
(
select row_number() over( order by salary) as rownumber,salary
from cummulativetable
),cte1(rownumber,salary,sumrows)
as
(
    select rownumber,salary,
    (
select sum(salary)
from
cte c1
where c1.rownumber <= c2.rownumber
)
    from cte c2
)

select * from cte1


Trick 3:
declare @salary int
declare @total int
set @total=0;
declare cur_cummulative cursor 
for
select salary from cummulativetable
for 
update of [cummulativesum]
open cur_cummulative 
fetch next from cur_cummulative into @salary

while @@fetch_status=0
begin
set @total+=@salary
update cummulativetable set cummulativesum=@total 
--where current of cur_cummulative
--where salary=@salary
--current of cur_cummulative

fetch next from cur_cummulative into @salary
end

select * from cummulativetable

close cur_cummulative
deallocate cur_cummulative


Both works...I have commented...you can use any one of it.
---.>update cummulativetable set cummulativesum=@total       where current of cur_cummulative
--->update cummulativetable set cummulativesum=@total  where salary=@salary