Returning first row of each repeating group values

create table repeatCheck
(id int identity(1,1),
email varchar(50),
[name] varchar(50) );

insert into repeatCheck values ('shamas@isl.com','Shamas');
insert into repeatCheck values ('shamas@hotmail.com','Shamas');
insert into repeatCheck values ('maria@isl.com','Maria');
insert into repeatCheck values ('azher@isl.com','Azher');
insert into repeatCheck values ('azher@hotmail.com.com','Azher');
insert into repeatCheck values ('khawar@isl.com','Khawar');
insert into repeatCheck values ('khalil@isl.com','Khalil');
insert into repeatCheck values ('khalil@hotmail.com.com','Khalil');
insert into repeatCheck values ('maria@hotmail.com.com','Maria');

Problem: Many of my friends ask me how can i get first or second row from table on some critaria which generates group like Category in Questions table.

Solution: In SQL Server 2005 we have many advance options which can do this. I am going to give you an example for that.


select * from repeatCheck;

--- Now return first row of repeating names

select *
from
(
select rank() over(partition by [name] order by email) as rownumber,email
from repeatCheck
) v
where v.rownumber = 1


delete from repeatCheck;
drop table repeatCheck;


You can see Rank function assign rank with the changing group like in the above example row number 1 and then 2 of rank value assigns form "Maria@isl.com" and "Maria@hotmail.com" but due to same name we have two id's of rank value. For next name we have rank value again starts from 1. At the last we can easily return top 1 of each group or any top number.

We can also do this in SQL Server 2000 or older but not Using Rank..