Need Query to retrive Unique records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Query to retrive Unique records

Author

Topic

pareekfranksoul
Starting Member

26 Posts

Posted - 2008-09-04 : 02:06:38

Below is my Table Records:
Name Email Score
madan madans5@yahoo.com 5056
madan madans5@yahoo.com 5018
shashi shashir6@yahoo.com 4784
rakesh raks784@yahoo.com 4773
shashi shashir6@yahoo.com 4743
rakesh raks784@yahoo.com 4732
Pinaki pinarray1@yahoo.com 4731
Pinaki pinarray1@yahoo.com 4690
candy candycheema1234@gmail.com 4687
candy candycheema1234@gmail.com 4684

I need query which returns below data:

madan madans5@yahoo.com 5056
shashi shashir6@yahoo.com 4784
rakesh raks784@yahoo.com 4773
Pinaki pinarray1@yahoo.com 4731
candy candycheema1234@gmail.com 4687

If particular email comes two or three times then i need top scorer email record.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:12:35

SQL 2005 specific solution

SELECT t.Name, t.Email, t.Score
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Name,Email ORDER BY Score DESC) AS RowNo,*
FROM YourTable) t
WHERE t.RowNo=1

sql 2000 or earlier solution

SELECT t.Name, t.Email, t.Score
FROM YourTable t
INNER JOIN (SELECT Name, Email, MAX(Score) AS MaxScore
            FROM YourTable
            GROUP BY Name, Email)tmp
ON tmp.Name=t.Name
AND tmp.Email=t.Email
AND tmp.MaxScore=t.Score