1

I'm familiar with how to aggregate rows as shown in this answer:

I'm also familiar with how to filter the aggregated results using a HAVING clause.

What I can't seem to grasp (so that it sticks) is how to filter rows based on values or comparisons other rows, without aggregating them.

I know the answer involves something about window functions or window clauses, and in fact I've done it successfully before. But it just doesn't seem to stick in my mind how it works; I feel I'm missing something fundamental.

To give an example, with the info:

 fruit_name | some_field 
------------+------------
 apple      |       3.25
 apple      |        6.8
 apple      |        0.7
 orange     |        2.6
 banana     |        3.5
 banana     |       2.49
 cherry     |          1
 grapefruit |        2.6
 grapefruit |        2.7

I want to get all rows with a row count (by fruit name) greater than 1, so it should look like:

 fruit_name | some_field 
------------+------------
 apple      |       3.25
 apple      |        6.8
 apple      |        0.7
 banana     |        3.5
 banana     |       2.49
 grapefruit |        2.6
 grapefruit |        2.7

What is the correct idiomatic way to do this?

(I'd like an SQL standard answer if possible, and if there's a simpler Postgres-specific way to do it I'd like to know that also.)

| improve this question | | | | |
  • 1
    Does the table have a PRIMARY KEY or a UNIQUE constraint? Please don't omit details. – ypercubeᵀᴹ Aug 15 '18 at 5:31
  • @ypercubeᵀᴹ in this specific case, no. But what I really want is to get a CLEAR grasp on partition by, over, and window functions. – Wildcard Aug 15 '18 at 6:31
  • how did you chose grapefruit = 2.6 and not orange = 2.6 – Evan Carroll Aug 15 '18 at 7:14
  • oh I see, when you greater than 1, you "mean more than one row of fruit_name" – Evan Carroll Aug 15 '18 at 7:18
  • what should happen if there are two values of some_field for the same fruit_name? – Evan Carroll Aug 15 '18 at 7:19
3
Select fruit_name, some_value
  From tooty_fruity      
  where fruit_name in (
    Select fruit_name from tooty_fruity
      Group by fruit_name
      having count(*) > 1)
| improve this answer | | | | |
2

You can use a simple INNER JOIN for this.

SELECT some_field, fruit_name
FROM tbl
INNER JOIN (
  SELECT fruit_name
  FROM tbl
  GROUP BY fruit_name
  HAVING count(*) > 1
) AS t
  USING (fruit_name);

Alternatively, you may like the array-syntax which is probably better for this kind of thing (certainly easier to use from the app)

SELECT fruit_name, ARRAY_AGG(some_field)
FROM tbl
GROUP BY fruit_name
HAVING count(*) > 1;
| improve this answer | | | | |
1

You can use a window function to count the number of fruit_names and then select those with a count greater than one:

SELECT fruit_name, some_field
FROM (
    SELECT fruit_name, some_field
         , COUNT(1) OVER (PARTITION BY fruit_name) as cnt
    FROM tbl
) AS T
WHERE cnt > 1
| improve this answer | | | | |

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.