CODEWITHSUNDEEP
sqlteradata
Omar
Omar

Reputation: 9

(SQL TERADATA) Having duplicate values in one column, achieve the output shown below

Original Table

Column 1 Column 2 Column 3
1 2019-03-09 13:03:59.000 2019-03-09 13:33:12.000
2 2019-03-09 13:33:12.000 2019-03-09 13:33:19.000
2 2019-03-09 13:33:19.000 2019-08-09 01:07:21.000

Output (Getting the value of column 2 from the first duplicate row and the value of column 3 from the second duplicate row):

Column 1 Column 2 Column 3
1 2019-03-09 13:03:59.000 2019-03-09 13:33:12.000
2 2019-03-09 13:33:12.000 2019-08-09 01:07:21.000

Upvotes: 0

Views: 286

Answers (1)

GoonerForLife
GoonerForLife

Reputation: 754

You can use window functions like row_number() to identify the row that you want to be in your result set. For example if you want to pick the latest record based on column2 and column2, you can do something like this:

SELECT *
FROM (
    SELECT Column1,
           column2,
           column3,
           ROW_NUMBER() OVER (PARTITION BY Column1 
                                 ORDER BY column2 DESC, column3 DESC) AS RN
    FROM Table_Name) t
WHERE t.RN = 1

Upvotes: 1

Related Questions