Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    11

    Unanswered: Selecting distinct columns

    Hi,

    I'm using simple select and insert from one database to another using federated objects.

    Database X
    The table from which I'm selecting has
    col1 + col2 = primary key

    Database Y
    Table in which I'm inserting is
    col1 + col3 = primary key

    Since col2 is no longer used in database Y
    How can I select distinct values from database X on col1 + col3 along with other columns

    Please let me know if I need to explain it with example.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If col1 + col3 in the old table are unique values, then you are home free. If they are not then only you can determine which rows from the old table are to be inserted into the new table.

    Andy

  3. #3
    Join Date
    Jan 2009
    Posts
    11
    Combination of col1+col3 should be unique as col3 is a timestamp.
    But my concern is how do I from a query something like:
    select distinct (col1+col3) , col4,col5,col6.
    As above syntax for distinct is not correct , so distinct will work on all columns

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If col1+col3 is unique, then col1,col3,col4,col5,etc,etc will also be unique so a straight select should do it

    insert into Y select col1,col3,.... from X

    Andy

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But my concern is how do I from a query something like:
    select distinct (col1+col3) , col4,col5,col6.
    If col1+col3 is unique, then col1,col3,col4,col5,etc,etc will also be unique so a straight select should do it
    If (col1+col3) is not unique and
    1) col4, col5, col6 are not neccesary selected from same row.
    Code:
    SELECT col1, col3, MAX(col4), MAX(col5), MAX(col6)
      FROM tableX
     GROUP BY col1, col3;
    2) col4, col5, col6 should be selected from same row.
    Code:
    SELECT col1, col3, col4, col5, col6
      FROM
         ( SELECT col1, col3, col4, col5, col6
                , ROW_NUMBER() OVER(PARTITION BY col1, col3) rn
             FROM tableX
         ) AS x
     WHERE rn = 1;

Posting Permissions

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