Dup Entry Query

  • Thread starter Thread starter Peter Carlson
  • Start date Start date
P

Peter Carlson

All of my records should have a unique ID (imported from a text file so
it's NOT a key). I am looking for duplicate ID#'s

My select should look something like:
SELECT Last, First, ID from Student_List where count(id) > 1 Group by ID
order by ID, Last, First;

However I get an error cannot have aggregate function in WHERE clause
(count(id) > 1)

How can I go about doing this one?

Peter
 
Peter said:
All of my records should have a unique ID (imported from a text file so
it's NOT a key). I am looking for duplicate ID#'s

My select should look something like:
SELECT Last, First, ID from Student_List where count(id) > 1 Group by ID
order by ID, Last, First;

However I get an error cannot have aggregate function in WHERE clause
(count(id) > 1)

How can I go about doing this one?
Click to expand...

SELECT Last, First, ID
FROM Student_List
GROUP BY ID, Last, First
HAVING COUNT(id) > 1
ORDER BY ID, Last, First;
 
This is great!...one more small favor...I need both records for the
select so I can figure out which one is in error

ie:
Carlson, Peter, 123
Carlson, Peter2, 123

Peter
 
Use a subquery in the WHERE clause

SELECT Last, First, ID
FROM Student_List
WHERE ID in (SELECT ID
FROM Student_List
GROUP BY ID
HAVING COUNT(id) > 1)
ORDER BY ID, Last, First;
 
Thanks, that worked.
Peter
Use a subquery in the WHERE clause

SELECT Last, First, ID
FROM Student_List
WHERE ID in (SELECT ID
FROM Student_List
GROUP BY ID
HAVING COUNT(id) > 1)
ORDER BY ID, Last, First;
Click to expand...
 
Back
Top