Duplicate value postgresql-CodePudding

I have an entry in the database

|   group   |   account   |   description   |   balance   |   balance1   |
 ---------- ------------- ----------------- ------------- -------------- 
|  123123  |       0     |      Name 1     |    1000.00  |      0       |
|  123123  |      777    |      Name 2     |     250.00  |      0       |
|  123123  |      999    |      Name 3     |       0     |     350.00   |
|  123000  |       0     |      Name 4     |     500.00  |      0       |
|  123000  |      567    |      Name 5     |       0     |     500.00   |

select

select * from table;

Gives exactly the same result as the example above.

I would like to get the result without duplicates in the "group" column. Here's one:

|   group   |   account   |   description   |   balance   |   balance1   |
 ---------- ------------- ----------------- ------------- -------------- 
|  123123  |       0     |      Name 1     |    1000.00  |      0       |
|          |      777    |      Name 2     |     250.00  |      0       |
|          |      999    |      Name 3     |       0     |     350.00   |
|  123000  |       0     |      Name 4     |     500.00  |      0       |
|          |      567    |      Name 5     |       0     |     500.00   |

That is, as you can see from the example, I want to remove only duplicate values from the first column, without affecting the rest. Also "group by", "order by" I can't use, as it will break the sequence of information output.

CodePudding user response：

Something like this might work for you:

with cte as 
    (
        SELECT goup, account, description, balance, balance1, 
        row_number() OVER(ORDER BY (SELECT NULL)) as rn 
        FROM yourtable
    )
SELECT case when LAG(goup) OVER (ORDER BY rn) = goup THEN NULL ELSE goup END AS goup,
    account, description, balance, balance1
FROM cte;

ORDER BY (SELECT NULL) is a fairly horrible hack. It is there because row_number() requires an ORDER BY but you specifically stated that you can't use an order by. The row_number() is however needed in order to use LAG, which itself requires an OVER (ORDER BY..).

Very much a case of caveat emptor, but it might give you what you are looking for.