patternsqlMinor
Show rows with non-unique value in one field
Viewed 0 times
showrowsuniquefieldnonwithvalueone
Problem
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:
I want to get all rows with a row count (by fruit name) greater than 1, so it should look like:
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.)
- 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.7I 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.7What 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.)
Solution
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)Code Snippets
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)Context
StackExchange Database Administrators Q#214945, answer score: 6
Revisions (0)
No revisions yet.