Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

postgresql

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

like image 572
user2628641 Avatar asked Apr 01 '16 14:04

user2628641


1 Answers

SELECT *  FROM product_price_info  WHERE name IN (SELECT name                 FROM product_price_info                 GROUP BY name HAVING COUNT(*) > 1) 
like image 100
Juan Carlos Oropeza Avatar answered Sep 20 '22 13:09

Juan Carlos Oropeza