I have a table with this sample data, where Id
and productname
are columns. I need an output where it returns Product1
when it has same Id along with other ProductNames
(see the next table for the output).
| Id | ProductName |
| ------------ | ------------ |
| ABC123 | Product1 |
| ABC123 | Product2 |
| XYZ345 | Product1 |
| PQR123 | Product1 |
| MNP789 | Product3 |
| EFG456 | Product1 |
| EFG456 | Product6 |
| EFG456 | Product7 |
| JKL909 | Product8 |
| JKL909 | Product8 |
| JKL909 | Product8 |
| DBC778 | Product9 |
| DBC778 | Product10 |
Desired output:
Id | ProductName |
---|---|
ABC123 | Product1 |
ABC123 | Product2 |
EFG456 | Product1 |
EFG456 | Product6 |
EFG456 | Product7 |
Basically it's grouped by Id when it has Product1
with other products.
I tired following query but its not giving desired result
select Id, ProductName
from tbl1
group by Id, ProductName
having count(ProductName) > 1
Thanks in advance
4条答案
按热度按时间jjhzyzn01#
Two typical options are:
ef1yzkbh2#
I reckon that, you had better to group the table without the having. The Query will be like the following:
hope this solve your question
vsdwdz233#
Below is one method to accomplish the task. The
DISTINCT
is not required with your sample data but is needed in case there are dups, which might be possible based on the sample data for other products.dw1jzc5e4#
Using
COUNT(*) OVER (PARTITION BY Id)
will give you theHAVING
-like functionality.