Results 1 to 6 of 6

Thread: Dupes with NULL

  1. #1
    Join Date
    Apr 2002
    Posts
    3

    Unanswered: Dupes with NULL

    "I am trying to select all of the duplicate values out of a table like this:



    field1 field2 field3 field4 field5
    jason anderson 266985421 Florida NULL
    Derek Lee 56898755 Louisiana 32
    jason anderson 266985421 Florida NULL





    the first and third are duplicate records but with the sql I have so far it doesn't reconize them as dupes becuase of the NULL.

    SELECT field1, field2, field3, field4, field5
    FROM table
    WHERE ((([field1]) In
    (SELECT [field1]
    FROM [table] AS TMP
    GROUP BY field1, field2, field3, field4, field5
    HAVING Count(*)>1 And [field1] = [table].[field1] AND ......and field5 = table.field5 )))
    ORDER BY field1,field2, .......field5

    what do I do???????????????????"

  2. #2
    Join Date
    Apr 2002
    Location
    VA
    Posts
    18

    Null

    select f1,f2,f3,f4,f5,count(*) from table group by f1,f2,f3,f4,f5
    having count(*) > 1

    this will give all duplicate records plus count of them

    best of luck
    Deepak Khattar

  3. #3
    Join Date
    Apr 2002
    Posts
    3
    I forgot one specific part..............there is a 6th field, and the 6th field is not one of the fields that can have a dupe, but i have to display it in the output

    field1 field2 field3 field4 field5 field6
    jason anderson 266985421 Florida NULL programmer
    Derek Lee 56898755 Louisiana 32
    jason anderson 266985421 Florida NULL dba


    so the output must display like this since this is a dupe record
    field1 field2 field3 field4 field5 field6
    jason anderson 266985421 Florida NULL programmer
    jason anderson 266985421 Florida NULL dba


    .........I have to show both fields

  4. #4
    Join Date
    Apr 2002
    Location
    VA
    Posts
    18

    Re: Null

    if you add f6 in the query , desired output will be generated
    Deepak Khattar

  5. #5
    Join Date
    Apr 2002
    Posts
    3
    I tryed adding f6 to the query and it did not work....the query also will not show BOTH the dupe records, and that is what I need, I need it to show the all 6 fields even though 5 of them have to match, the 6th field does not have to match. so my ouput needs to be as such.......

    fied1 field2 field3 field 4 field 5 field6
    match match match match match match doesn't matter
    match match match match match match doesn't matter

    ............and my other probelm was that if the field was a null then it would not count as a dupe..........do you have any suggestions

  6. #6
    Join Date
    Apr 2002
    Location
    VA
    Posts
    18
    Try this


    select a.f1, a.f2,a.f3,a.f4,f5=ISNULL(a.f5,'OOPS') ,b.f6
    FROM (select f1, f2,f3,f4,f5=ISNULL(f5,'OOPS'),cc=count(*) from #temp group by f1, f2,f3,f4,f5=ISNULL(f5,'OOPS') having count(*) > 1) AS A,
    (select f1, f2,f3,f4,f5=ISNULL(f5,'OOPS'),f6 from #temp ) AS B
    where a.f1 = b.f1 and
    a.f2 = b.f2 and
    a.f3 = b.f3 and
    a.f4 = b.f4 and
    a.f5 = b.f5


    NULL is hanndled by converting it to 'OOPS' , if you don't like OOPS in your output U can use CASE statement to convert it back to null in FIRST SELECT statement

    In case f6 is also having Dup then use Distinct in first select statement
    Deepak Khattar

Posting Permissions

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