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

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.