Using Ranking Functions to Deduplicate Data

// Input : A A B B C D D E
// Output: A B C D E

// Create a sample test data

declare @AlphaList table (AlphaKey char);

insert into @AlphaList(AlphaKey) values ('A');
insert into @AlphaList(AlphaKey) values ('A');
insert into @AlphaList(AlphaKey) values ('B');
insert into @AlphaList(AlphaKey) values ('B');
insert into @AlphaList(AlphaKey) values ('C');
insert into @AlphaList(AlphaKey) values ('D');
insert into @AlphaList(AlphaKey) values ('D');
insert into @AlphaList(AlphaKey) values ('E');

select AlphaKey from @AlphaList order by 1;

//Returns the output   : 1 A  2 A  3 B  4 B  5 C  6 D  7 D  8 E
select
ROW_NUMBER() over (order by AlphaKey) as RowNumber, AlphaKeyfrom @AlphaList;
//Returns the output   : 1 1 A  1 2 A   3 3 B   3 4 B  5 5 C  6 6 D  7 7 D  8 8 E
select
RANK() over (order by AlphaKey) as Rank, ROW_NUMBER() over (order by AlphaKey) as RowNumber, AlphaKeyfrom @AlphaList;