Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2015
    Posts
    27

    Post Unanswered: Converting duplicates records into non duplicates

    Is there a query or a way to convert duplicates value in a column to non duplicates.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat couldn't you just do a Group By?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by VLOOKUP View Post
    Pat couldn't you just do a Group By?
    Until we have more details about what the OP (Original Poster, aka Kahn16) wants, all that I'm sure of is that it is possible to convert duplicate values into unique values. Once we know more about what the OP wants then we can focus on how to accomplish it, but for now "Yes" is all that I know for sure.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2015
    Posts
    27
    I have populated a column with random numbers but it contain duplicate values. I wanted to know how those duplicate values could be converted or changed so there wont be any dupes in the field/column...thanks

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does the table have a primary key now?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2015
    Posts
    27
    No primary key

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the requirements that you have disclosed, this may do what you need:
    Code:
    IF Object_Id('foo', 'U') IS NOT NULL
       DROP TABLE foo
    GO
    
    CREATE TABLE foo (
       bar INT
       )
    
    INSERT INTO foo (bar) VALUES
       (155957), (155957), (155968)
    ,  (155974), (155976), (15599)
    ,  (155990), (155997), (155997)
    ,  (156005), (156008)
    
    SELECT bar FROM foo
    
    ALTER TABLE foo
      ADD bat INT IDENTITY
    
    SELECT bar, bat FROM foo
    
    UPDATE foo
      SET bar = bat + (SELECT Max(z.bar) FROM foo AS z)
      WHERE EXISTS (SELECT *
         FROM foo AS y
    	 WHERE  y.bar = foo.bar
    	   AND  foo.bat < y.bat)
    
    SELECT bar, bat FROM foo
    
    ALTER TABLE foo
       DROP COLUMN bat
    
    SELECT *
       FROM foo
       ORDER BY bar
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    This is called an additive congruency generator, as opposed to a random number generator. Here is the formula for a 31-bit additive congruency generator. It does not repeat until it has completed a full cycle.

    UPDATE generator
    SET keyval = keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2) * 2^30;

    Here is the same algorithm implemented in C.

    int asequence()
    {static int n = 1;
    n = n>>1 | (( n^n>>3 ) & 1) << 30;
    return n;}

    There are other formulas for different length integers.

  10. #10
    Join Date
    Jan 2015
    Posts
    27
    thanks for the update much appreciated.
    I am still verifying all the records in that field to make sure there are no duplicates, it seems to be
    working so far. I will keep u updated, unless you have a script to run for checking for duplicates record in a field.

    Thanks again for your help.

  11. #11
    Join Date
    Jan 2015
    Posts
    27
    by the way do I need to replace 'foo' and 'bat' with my table name and column name

  12. #12
    Join Date
    Jan 2015
    Posts
    27
    MOD is no recognize in my sql 2008 query
    I am assuming that 'generator' is the table name and 'keyval' is the column name in your example sql query

  13. #13
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    MOD is not recognize in my sql 2008 query
    That was copied from one of my books; that is ANSI/ISO Standard SQL, not dialect.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Khan16 View Post
    by the way do I need to replace 'foo' and 'bat' with my table name and column name
    Certainly you need to use your own table and column names.

    My proposed solution simply solves the problem as stated, Joe Celko's elegantly goes several steps further.

    If you want a cycle that will use all of the integers in the range before it repeats, then Joe's answer is a better fit for your problem than mine was... Joe's the mathematician in the DBForums family, as well as the crotchety old phart that rails at everyone to keep off his lawn and also to follow standards that most mortals didn't realize existed!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Just curious as to how it is possible to get dupes from randomly generated numbers?

Tags for this Thread

Posting Permissions

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