CODEWITHSUNDEEP
sqlpostgresql
Jens
Jens

Reputation: 291

How to rewrite some values in query? - PostgreSQL

I have got a query which result is in table1

-------------------------
|column1|column2|column3|
-------------------------
|   v1  |   30  |  40   |
|   v1  |   34  |  41   |
|   v1  |   35  |  42   |
|   v2  |   30  |  40   |
|   v2  |   34  |  41   |
|   v2  |   35  |  42   |
-------------------------

I want to change duplicated values to NULL in first column, (i.e).

-------------------------
|column1|column2|column3|
-------------------------
|   v1  |   30  |  40   |
| null  |   34  |  41   |
| null  |   35  |  42   |
|   v2  |   30  |  40   |
| null  |   34  |  41   |
| null  |   35  |  42   |
-------------------------

What should I do with table1?

Upvotes: 0

Views: 64

Answers (4)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

SELECT CASE WHEN RN=1 THEN
          "column1"
       ELSE
          NULL
       END AS "column1",
       "column2", "column3"
FROM
(
SELECT 
"column1", "column2", "column3",
ROW_NUMBER() OVER  (PARTITION BY "column1" ORDER BY "column1") AS RN
FROM Table1
) AS T

Ouput

column1 column2 column3
v1      30      40
(null)  34      41
(null)  35      42
v2      30      40
(null)  34      41
(null)  35      42

Demo

http://sqlfiddle.com/#!17/5b2e1/9

Upvotes: 1

D-Shih
D-Shih

Reputation: 46229

You can try this query.

using ROW_NUMBER function to make rowNumber,then CASE When Expression to show the first column1 else be null

ORDER BY (SELECT 1) can let your data be the original order.

select CASE WHEN rn = 1 THEN  column1 ELSE NULL END,
       column2,
       column3
from 
(
  SELECT column1,
         column2,
         column3,
         ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY (SELECT 1)) rn 
  FROM T
) t

sqlfiddle:http://sqlfiddle.com/#!15/fcf3d/1

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use row_number() function:

select (case when row_number() over (partition by col1 order by col1) > 1 
             then null else col1 end) col1, 
        col2, col3
from table t;

However, you can also use lag() function 

select (case when lag(col1) over (partition by col1 order by col1) = col1 
                  then null else col1 end) col1, 
        col2, col3
from table t;

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

try this:

SELECT CASE WHEN D.RN = 1 THEN column1 ELSE NULL END AS column1
    ,column2
    ,column3
FROM(
    SELECT *
        , ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY column1)RN
    FROM Your_table
    )D

Upvotes: 1

Related Questions