(apologies for the ugly title of this question)
In my example below, I want to keep rows as combination of product, date, (and value?) for which a rownnum = 2 exists
| product | date | value | rownum |
|---|---|---|---|
| ball | 2021-09-01 | 3.5 | 1 |
| ball | 2021-09-01 | 2.5 | 2 |
| ball | 2021-10-01 | 3.5 | 1 |
| tshirt | 2021-09-01 | 10.0 | 1 |
| tshirt | 2021-09-01 | 10.0 | 2 |
| ball | 2021-11-01 | 3.25 | 1 |
| tshirt | 2021-11-01 | 16.0 | 1 |
| ball | 2021-12-01 | 5.0 | 1 |
| ball | 2021-12-01 | 2.75 | 2 |
So I would like to get the following table:
| product | date | value | rownum |
|---|---|---|---|
| ball | 2021-09-01 | 3.5 | 1 |
| ball | 2021-09-01 | 2.5 | 2 |
| tshirt | 2021-09-01 | 10.0 | 1 |
| tshirt | 2021-09-01 | 10.0 | 2 |
| ball | 2021-12-01 | 5.0 | 1 |
| ball | 2021-12-01 | 2.75 | 2 |
Can someone please get me on track with keywords to google? Or maybe show an example?
Many thanks.
CodePudding user response：
WITH eligible as (
SELECT product, date
FROM [Table]
GROUP BY product, date
HAVING COUNT(*) >= 2
)
SELECT t.*
FROM [Table] t
INNER JOIN eligible e on e.product = t.product and e.date = t.date