Web Development

Ask something
Discussion Locked

SQL Duplicates

0 helpful
6 replies

by jerry e

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How to

Table is not indexed. Columns are fname,lname,mname,dob,facility,startdate,stopdate.

There are multiple records with same fname,lname,mname,dob and different facility also
records with only one fname,lname,mname,dob

question is:How to find fname,lname,mname,dob that have more than one record and different facility.
using standard SQL (Microsoft Transact SQL)

6 replies

by dushy_ant

Write the Query

Select fname,lname,mname,dob,facility,startdate,stopdate, count(*)
from
Group By fname,lname,mname,dob,facility,startdate,stopdate
where count(*) > 1

This will give the records where the Names are Duplicated

by jerry e

Dushy,

Tried the SQL and It does not like the Where clause I tried Having and it worked. Is this the way it should be.

select [Last Name], [First Name], Middle , DOB, Count(*)

from vinect

group by [Last Name],[First Name], Middle, DOB

having count(*) > 1

by sl-campbell

Possible syntax are:

SELECT Fields FROM Table WHERE Field = Criteria1

SELECT Fields FROM Table WHERE Field = Criteria1 GROUP BY Fields

SELECT Fields FROM Table WHERE Field = Criteria1 GROUP BY Fields HAVING Field = Criteria2

SELECT Fields FROM Table GROUP BY Fields HAVING Field = Criteria2

This discussion is closed to new replies.