1

Figure a table with the following data, how to exclude 'alone' values?

id; data
(1, 'foo'),
(2, 'foo'),
(3, 'foo'),
(4, 'bar'),
(5, 'bar');
(6, 'jak');

I try with this:

select id, data, row_number() over (partition by data)
from t;

 -- RESULT
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3
6   jak     1

 -- EXPECTED (exclude partition with one row)
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3

row_number() gives an index to rows in each partition.

How to have the number of rows by partition? Some kind of WHERE partition_count > 1.

fiddle

2

Use count as a window function:

SELECT id, data
FROM (SELECT id, data,
             count(*) OVER (PARTITION BY data) AS c
      FROM t) AS q
WHERE c > 1;
  • So simple, thanks. 'c' is missing from the SELECT in your answer. – Slim Sep 20 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.