I am trying to find non distinct rows in a table.  For e.g
1.A list of all same Social Security numbers
2.A list of all same last names


Could someone please assist with what is the sql for this.

Thanks

S Commar

All last names that occur more than once:

SELECT last_name FROM MyTable
GROUP BY last_name
HAVING COUNT(*) > 1


All rows with a non-distinct last name:

SELECT T1.* FROM MyTable T1
WHERE T1.last_name IN
(SELECT T2.last_name FROM MyTable T2
GROUP BY T2.last_name
HAVING COUNT(T2.*) > 1)


Show quoteHide quote

If I use the following query

SELECT taxidnumber FROM nonrefven2003
GROUP BY taxidnumber
HAVING COUNT(*) > 1;

Can I pull up the complete info . ie. all the other colums assocaited
with the above records as well. That is from the nonrefven2003 table I
want to select all the colums that get pulled up by the above mentioned
query which is selected duplicate records.

Thanks again for your help

S Commar



*** Sent via Developersdex http://www.developersdex.com ***

3 Questions on db Theory/Design
restoring db without log
Problems with NOT IN
Upgrade from SQL Server 7.0 to SQL Server 2000
How to get the count of rows selected in a cursor