khajlk
I have following sample data in my PostgreSQL 11 database:
<>https://dbfiddle.uk/?rdbms=postgres_11&fiddle=407b58bc609be34e781f8b6aa4ec5dc7
I would like to divide/split above data into two parts, the one with unique ids, and the other with repeated ids.
My expected output is:
```
id val1 val2
1 70.4 40.6
2 68.4 35.4
3 34.6 79.5
5 129.7 71.7
```
and
```
id val1 val2
4 0 5
4 67 0
4 0 72.7
4 15.2 0
6 24.3 0
6 0 176.1
```
Can someone suggest me how this can be achieved?
Top Answer
Erwin Brandstetter
You can use [`EXISTS`](https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS) to get all in a single query:
<>https://dbfiddle.uk/?rdbms=postgres_11&fiddle=50191c86565db1012812c05c780550fa&hide=11
`ctid` is my fallback since you didn't declare a `PRIMARY KEY` (or any `UNIQUE` condition). There is another question discussing its nature:
@@@ answer 112
I added the column `repeated` to mark repeated rows. But you don't have to. The `EXISTS` expression can just go into the `ORDER BY` clause:
<>https://dbfiddle.uk/?rdbms=postgres_11&fiddle=50191c86565db1012812c05c780550fa&hide=7
And I appended `val1` and `val2` to `ORDER BY` to get a stable sort order in the result. Also optional.
Answer #2
Jack Douglas
There are lots of ways to do this. Here is a solution using the windowing function version of `count`:
<>https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f45378c667bb9dc62280f52e9310eda1&hide=3