Postgres: select all row with count of a field greater than 1

i have table storing product price information, the table looks similar to, (no is the primary key)

no   name    price    date
1    paper   1.99     3-23
2    paper   2.99     5-25
3    paper   1.99     5-29
4    orange  4.56     4-23
5    apple   3.43     3-11

right now I want to select all the rows where the “name” field appeared more than once in the table. Basically, i want my query to return the first three rows.

I tried:

SELECT * FROM product_price_info GROUP BY name HAVING COUNT(*) > 1

but i get an error saying:

column “product_price_info.no” must appear in the GROUP BY clause or be used in an aggregate function

Answer

SELECT * 
FROM product_price_info 
WHERE name IN (SELECT name 
               FROM product_price_info 
               GROUP BY name HAVING COUNT(*) > 1)

Advertisement

Answer