Postgresql – Show rows with non-unique value in one field

postgresqlselectwindow functions

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

Show only duplicate values

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.)

Best Answer

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)

Related Solutions

Postgresql – Calculate value differences of rows before and after bounded by multiple referenced rows

SELECT *
      ,CASE WHEN location = 'Loc_B' OR right_tree = 0 AND left_tree = 0 THEN NULL::int
            ELSE CASE WHEN @right_diff < @left_diff THEN right_diff ELSE left_diff END
       END AS min_diff
FROM  (
   SELECT *
         ,CASE WHEN right_tree > 0 THEN start_time - right_end ELSE 1000 END AS right_diff
         ,CASE WHEN left_tree  > 0 THEN end_time  - left_start ELSE 1000 END AS left_diff
   FROM  (
      SELECT *
            ,first_value(end_time)   OVER (PARTITION BY right_tree ORDER BY start_time) AS right_end
            ,first_value(start_time) OVER (PARTITION BY left_tree  ORDER BY start_time DESC) AS left_start
      FROM  (
         SELECT *
               ,count(location = 'Loc_B' OR NULL) OVER (PARTITION BY id ORDER BY start_time) AS right_tree
               ,count(location = 'Loc_B' OR NULL) OVER (PARTITION BY id ORDER BY start_time DESC) AS left_tree
         FROM   travel
         ) a
      ) b
   ) c
   ORDER  BY id, start_time;

Produces your result exactly.

1000 is just some "higher value than any other". Since your actual problem seems to operate with times and intervals infinity would be the perfect choice.

Related answer with detailed explanation how groups (right_tree and left_tree here) are formed:
Select longest continuous sequence

Mysql – Get rows with minimum value

select employee_id, min(score) from 
employee 
join employee_company on employee.employee_id=employee_company.employee_id 
join company on company.company_id=employee_company.company_id 
join score on score.employee_id=employee.employee_id and score.company_id=company.company_id 
join factor_company on factor_company.company_id=company.company_id
join factor on factor_company.factor_id=factor.factor_id
where factor.factor_id = factor_id

group by employee_id

In the above statement, you take all employee ids and their scores from the query you gave, and take the minimum score for each employee ID. Adapt as needed if it's not exactly that that you want.

Related Question