This is probably so easy it will be stupid but I cannot get anything to
work.
I need to find, from one table, all duplicate names. I pull the entire list
like this:
SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
FROM Attendants
What I need from this is a list where the names of the clients appear more
than once. I know there has to be an easy way to do this. Any quick help'SELECT First_Name,last_Name, count(*)
FROM Attendants
group by first_name, last_name
having count(*) > 1
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||SELECT ID, RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name) , Date_of_Birth
FROM Attendants AS a
WHERE EXISTS (SELECT RTRIM(b.First_Name) + ' ' + RTRIM(b.Last_Name)
FROM Attendants AS b
WHERE a.First_Name = b.First_Name and
a.Last_Name = b.Last_Name
GROUP BY RTRIM(b.First_Name) + ' ' +
RTRIM(b.Last_Name)
HAVING COUNT(*) > 1 )
ORDER BY RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name)
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||Thanks Geoff
Works GREAT!
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:u0lwu%237%23HHA.1164@.TK2MSFTNGP02.phx.gbl...
> SELECT First_Name,last_Name, count(*)
> FROM Attendants
> group by first_name, last_name
> having count(*) > 1
>
> --
> Geoff Chovaz
> MCTS: SQL Server 2005
> MCITP: Database Administrator
> MCITP: Database Developer
>
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
>> This is probably so easy it will be stupid but I cannot get anything to
>> work.
>> I need to find, from one table, all duplicate names. I pull the entire
>> list like this:
>> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
>> FROM Attendants
>> What I need from this is a list where the names of the clients appear
>> more than once. I know there has to be an easy way to do this. Any quick
>> help'
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment