Development

Question 1

How do you delete perfect dups from a table

Answer 1

create table #master(code int, des varchar(30));
insert into #master
select 1, 'one'
union all
select 2, 'two'
union all
select 3, 'three'
union all
select 4, 'four'
union all
select 2, 'two'
union all
select 4, 'four'

with s as (
select *, row_number() OVER(partition by code, des order by code, des) as row_num
from #master
)
delete from s where row_num > 1

Question 2

What’s the difference between rank() vs dense_rank?

Answer 2

They’re both the same until a tie in values is encountered. dense_rank values are always consecutive while rank will skip values if there’s a tie. example:
rank: 1,2,3,4,4,6
dense_rank: 1,2,3,4,4,5

Question 3

what is ntile() function?

Answer 3

For each partition, rows are distributed to groups based on the order by clause. the number of groups are passed as a parameter to the tile() function. A partition is not required.