Selecting distinct columns

mka12 · #1 (**permalink**) 04-20-09, 07:47

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.

ARWinner · #2 (**permalink**) 04-20-09, 08:22

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

mka12 · #3 (**permalink**) 04-21-09, 00:45

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

ARWinner · #4 (**permalink**) 04-21-09, 08:20

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

tonkuma · #5 (**permalink**) 04-22-09, 23:13

Quote:

But my concern is how do I from a query something like:
select distinct (col1+col3) , col4,col5,col6.

Quote:

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech