Duplicate value postgresql

Question

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.

SQL wasn't designed to do this kind of formatting. That's much better done in your application when you display those results. — user330315
– user330315, Commented Nov 17, 2022 at 16:04
@a_horse_with_no_name I am writing my application in Spring. The point is that this is a table that I need to parse from xml. Everything is fine with parsing) The data is written as in my example (this example is not shown here). But when in my application I try to collect data in a Map, they are not displayed correctly on the front. So I had this idea — Alexander
– Alexander, Commented Nov 17, 2022 at 16:28

Jonathan Willcock · Accepted Answer · 2022-11-17 16:37:55Z

1

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.

answered Nov 17, 2022 at 16:37

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

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

1 Comment

Marmite Bomber Over a year ago

order by NULL works as well BTW, but @Alexander needs to use soem columns from the table to get deterministic result; e.g. order by account

Collectives™ on Stack Overflow

Duplicate value postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related