Professional Web Applications Themes

Finding Duplicate Values in a table - Microsoft SQL / MS SQL Server

Hello, I would like to find only the values in a table that are duplicated (or shown more than once). How can I do this through SQL statement? Thank you, Dan Sullivan...

  1. #1

    Default Finding Duplicate Values in a table

    Hello,
    I would like to find only the values in a table that are
    duplicated (or shown more than once). How can I do this
    through SQL statement?

    Thank you,
    Dan Sullivan
    Dan Guest

  2. #2

    Default Re: Finding Duplicate Values in a table

    Example:
    USE Northwind
    SELECT City FROM Customers
    GROUP BY City
    HAVING COUNT(*) > 1

    will give you all the cities that appear more than once in the customer
    table.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Dan Sullivan" <com> wrote in message
    news:00d401c34c75$7f421e30$gbl... 


    Jacco Guest

  3. #3

    Default Re: Finding Duplicate Values in a table

    Try:

    select
    MyCol
    , count (*)
    from
    MyTable
    group by
    MyCol
    having
    count (*) > 1

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Dan Sullivan" <com> wrote in message news:00d401c34c75$7f421e30$gbl...
    Hello,
    I would like to find only the values in a table that are
    duplicated (or shown more than once). How can I do this
    through SQL statement?

    Thank you,
    Dan Sullivan

    Tom Guest

  4. #4

    Default Re: Finding Duplicate Values in a table

    I would think you could use 'group by' and 'having count()', e.g.

    select theColumn from theTable
    group by theColumn
    having count(theColumn) > 1

    regards, marty nicholson

    "Dan Sullivan" <com> wrote in message
    news:00d401c34c75$7f421e30$gbl... 


    Martin Guest

Similar Threads

  1. Finding the most recent record in a table with otherwiseidentical field values
    By kbergstrom in forum Coldfusion Database Access
    Replies: 7
    Last Post: September 22nd, 08:55 PM
  2. duplicate parameter values
    By spitefulgod in forum ASP Database
    Replies: 0
    Last Post: July 19th, 11:55 PM
  3. Replies: 6
    Last Post: January 31st, 02:48 AM
  4. Help: finding values in a LIST
    By Word of Mouth Productions in forum Macromedia Director Lingo
    Replies: 7
    Last Post: August 14th, 04:05 PM
  5. Finding null values in a large table
    By Tim Dry in forum Oracle Server
    Replies: 5
    Last Post: December 6th, 10:53 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139