Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: Select DISTINCT on multiple columns is not returning distinct rows?

    Hi, I have the following script segment which is failing:

    CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

    INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache



    When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."



    Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

    The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

    Any ideas?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Scale and precision maybe?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2007
    Posts
    2
    Thanks for the quick reply - indeed there was an implicit conversion happening after the select distinct that caused the rows to no longer be unique.

    After creating the correct scale and precision on my temp table all is resolved.

Posting Permissions

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