CODEWITHSUNDEEP
mysqlsql
Viralk
Viralk

Reputation: 2389

Get all duplicates rows in sql

I want to fetch all duplicate values in sql. I searched at many places, but I don't get what I exactly want. My table is like this:

company_id | supplier_id | company_name | organisation_no | type | sold_date
----------------------------------------------------------------------------
121234     | 934575      | fgdf         | 12345           | sold | 2011-12-2
214365     | 423234      | sdgd         | 5678            | sold | 2011-12-2
546534     | 234234      | bvcv         | 3333            | sold | 2011-12-2
276345     | 243324      | dfgd         | 12345           | sold | 2011-12-2
432642     | 567647      | ghmj         | 12345           | sold | 2011-12-2
846578     | 365356      | egff         | 3333            | sold | 2011-12-2
254334     | 346535      | yuuy         | 7890            | sold | 2011-12-2

The solution I found is like this:

organisarion_no | count(organisation_no)
----------------------------------------
3333            | 2
12345           | 3

But I want exactly like this:

company_id | supplier_id | company_name | organisation_no | type | sold_date
----------------------------------------------------------------------------
546534     | 234234      | bvcv         | 3333            | sold | 2011-12-2
846578     | 365356      | egff         | 3333            | sold | 2011-12-2
121234     | 934575      | fgdf         | 12345           | sold | 2011-12-2
276345     | 243324      | dfgd         | 12345           | sold | 2011-12-2
432642     | 567647      | ghmj         | 12345           | sold | 2011-12-2

Please help. Thanks in advance.

Upvotes: 2

Views: 3654

Answers (2)

mcha
mcha

Reputation: 2998

SELECT * FROM table t 
 WHERE organisation_no IN 
       (SELECT organisation /*from the solution you have found*/)

Upvotes: 0

bpgergo
bpgergo

Reputation: 16037

select * from your_table
where organization_no in 
   (select organization_no 
   from your_table
   group by organization_no
   having count(*) > 1)

Upvotes: 6

Related Questions