Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Splitting Dataset in 2 parts

    Hi,

    I have 5 records in a table called "testing" as such:

    LetterCd contact_id
    x 1
    x 2
    x 3
    x 4
    x 5

    How can I split this dataset so that three records have LetterCd = 'y' and the other 2 records have LetterCd = 'x'? The number of records can vary, but the idea is the same: split the values in the LetterCd column evenly (50% = 'x', 50% = 'y'). LetterCd is varchar(25) and contact_id is int. I can't update the data, it would have to be a SELECT statement with some sort of CASE statement to get the remainder of the record count?

    TIA
    Last edited by naceBal; 03-22-12 at 18:28.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    ;WITH CTE AS
    (SELECT LetterCd, contact_id,
    	ROW_NUMBER() OVER (ORDER BY contact_id) AS RowNum
    FROm #DaTable
    ),
    NumRecords AS
    (SELECT COUNT(*) as NumRecords
    FROM CTE)
    SELECT LetterCd, contact_id
    FROM CTE, NumRecords
    WHERE RowNum <= NumRecords.NumRecords / 2
    UNION ALL
    SELECT 'Y', contact_id
    FROM CTE, NumRecords
    WHERE RowNum > NumRecords.NumRecords / 2
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    "Why so seeeeeeeeeeeeeeeerious"

    Code:
    CREATE TABLE #fubar(LetterCd char(1), contact_id int)
    GO
    
    INSERT INTO #fubar(LetterCd, contact_id)
    SELECT 'x',	 1 UNION ALL
    SELECT 'x',	 2 UNION ALL
    SELECT 'x',	 3 UNION ALL
    SELECT 'x',	 4 UNION ALL
    SELECT 'x',	 5
    GO
    
    SELECT contact_id
    		, CASE WHEN contact_id/2.00 = FLOOR(contact_id/2.00) 
    			  THEN 'x' 
                               ELSE 'y' 
                      END AS ContactId
    FROM #fubar
    GO
    
    DROP TABLE #fubar
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    "Why so seeeeeeeeeeeeeeeerious"
    Because the values of the id's in the records could be like this:

    id
    5
    6
    56
    58
    59
    10123
    10124
    10125
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    what?

    You don't think odd and even wouldn't get you your 50% or damn close to it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE #foo (
       LetterCd     CHAR(1)	NULL
    ,  contact_id	INT     NOT NULL
       PRIMARY KEY (contact_id)
       )
       
    INSERT INTO #foo (LetterCd, contact_id)
       VALUES ('x', 1), ('x', 2), ('x', 3)
    ,     ('x', 4), ('x', 5)
    
    SELECT *
    ,  CASE
          WHEN 1 = ROW_NUMBER() OVER(ORDER BY contact_id) % 2 THEN 'Y'
          ELSE 'X'
       END AS NewLetterCd
       FROM #foo
    
    DROP TABLE #foo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Nice coding, Pat. A beer coming your way
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Brett Kaiser View Post
    You don't think odd and even wouldn't get you your 50% or damn close to it?
    No, I just don't want to rely on the coincidental values of the Id's to split the records in two equal halves. When and while that works, it's just a coincidence. Suppose, this code would have been used to create the table and the Id:
    Code:
    CREATE TABLE MyTable(
       Id     INT     IDENTITY(0, 2)   NOT NULL, 
       ....
       CONSTRAINT pk_MyTable PRIMARY KEY (Id)
    )
    Pat came up with a more elegant way and one that will work always: give each row a sequential number, when that number is even, treat it as half "X", otherwise (odd) treat it as half "Y".
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Mar 2003
    Posts
    97
    I've implemented PatP's suggestion, and so far it seems to work well.

    Thanks everyone!

Posting Permissions

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