Results 1 to 3 of 3

Thread: Duplicates

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Duplicates

    Hi I am trying to find all the duplicates in the School_tbl because from what I see there is allot how would I go about dong that
    and then once I find them how do I delte them all at once





    Code:
    SELECT  COUNT([Student ID]) AS StudentIDSchool, [School Name], [School Phone], [School Address], [School City], [School State], [School Zip]FROM  School_tblGROUP  BY [School Name], [School Phone], [School Address], [School City], [School State], [School Zip]

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    desireemm, what do you consider a duplicate? Having [Student ID] in the table more than once? Here is a basic way to find duplicates:
    Code:
    SELECT [Student ID], COUNT(*) AS CNT
    FROM School_tbl
    GROUP BY [Student ID]
    HAVING COUNT(*) > 1
    The key part is the HAVING clause. This will filter rows AFTER the GROUP BY (as opposed to the WHERE clause which will filter BEFORE the GROUP BY).

    Hopefully this will point you in the right direction to get the result that you want.

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    if ur using 2005 and above use row_number
    select * from (select * , row_number()over(partition by studentid order by studentid) as rid from urtablename)s where rid > 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •