How to rewrite some values in query? - PostgreSQL

Question

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?

Yogesh Sharma · Accepted Answer · 2018-04-27 05:27:48Z

1

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;

answered Apr 27, 2018 at 5:27

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DineshDB · Accepted Answer · 2018-04-27 05:27:44Z

1

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

answered Apr 27, 2018 at 5:27

DineshDB

6,2238 gold badges38 silver badges54 bronze badges

Comments

D-Shih · Accepted Answer · 2018-04-27 05:32:46Z

1

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

answered Apr 27, 2018 at 5:32

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Comments

Jay Shankar Gupta · Accepted Answer · 2018-04-27 05:37:24Z

1

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

answered Apr 27, 2018 at 5:37

Jay Shankar Gupta

6,1281 gold badge14 silver badges31 bronze badges

Collectives™ on Stack Overflow

How to rewrite some values in query? - PostgreSQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related