CODEWITHSUNDEEP
ruby-on-railspostgresql
Sharpless512
Sharpless512

Reputation: 3222

Retrieve duplicates and show them

I have this problem where I want to output rows that have the same name in another row. If the row does not have an another row with the same name, do not retrieve that row.

|id|name|
|1|battlefield review|
|2|call of duty review|
|3|battlefield review|
|4|saints row review|
|5|call of duty review|
|6|Overwatch review|

The rows I want to retrieve are:

|id|name|
|1|battlefield review|
|3|battlefield review|
|2|call of duty review|
|5|call of duty review|

This is what I got so far

@unique_ids = ExternalReview.select("DISTINCT ON (name) id").map(&:id)
@external_reviews = ExternalReview.where.not(id: @unique_ids).order("name ASC").paginate per_page: 25, page: params[:page]

I hope you can help me

Upvotes: 0

Views: 33

Answers (1)

Nik
Nik

Reputation: 371

SELECT id, 
       NAME 
FROM   table1 
WHERE  NAME IN (SELECT NAME 
        FROM   table1 
        GROUP  BY NAME 
        HAVING Count (DISTINCT NAME) > 1)

Try self join as above

Upvotes: 1

Related Questions