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..

Find repeating values from table

Today i am going to discuss a very common problem that takes too much time of developers to write code and find soution

Problem: How can i find duplicate and return values from table data
Solution: Using SQL Server following is the example to find duplicating values.


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

insert into repeatCheck values ('shamas@isl.com');
insert into repeatCheck values ('shamas@isl.com');
insert into repeatCheck values ('maria@isl.com');
insert into repeatCheck values ('azher@isl.com');
insert into repeatCheck values ('khawar@isl.com');
insert into repeatCheck values ('khalil@isl.com');
insert into repeatCheck values ('maria@isl.com');


select * from repeatCheck

--- Now return all repeating emails

select email,count(email) as emailcount
from repeatCheck
group by email
having count(email) > 1


As you can see how simple is to return duplicate values for different email addresses

Difference between Editions

1. SQL Server 2008 Features Comparison
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
http://msdn.microsoft.com/en-us/library/cc645993.aspx

2. SQL Server 2005 Features Comparison
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

3. SQL Server 2000 Features Supported
http://msdn.microsoft.com/en-us/library/aa175266(SQL.80).aspx