CODEWITHSUNDEEP
mysqlsql
t777
t777

Reputation: 3329

How to SQL select duplicates by one field and differs by another

I have the table person_log with the following fields:

with some example data

1;012545878;homer;simpson;eating
2;812345222;homer;simpson;watching tv
3;312322578;marge;simpson;cooking
4;114568568;bart;simpson;skating
5;015345345;bart;simpson;skating

Now I need to select all recordsets where first_name and last_name is the same and occurs at least two times, but action differs.

Resulting in selecting ids 1 and 2.

How can I do that? TIA?

Upvotes: 0

Views: 45

Answers (3)

my779
my779

Reputation: 79

You can use row_number(), partition would let you group by firstname,lastname, and action:

select distinct id 
from (select pl.*,
         row_number() over (partition by first_name, last_name, action) as seqnum
  from person_log pl
 ) R
where seqnum > 1;

Upvotes: 0

user3754008
user3754008

Reputation: 285

You can group by those field you want then have count greater or equal's to 2 as a table to be join to your main person_log table.

SELECT * FROM person_log p
JOIN (
SELECT first_name, last_name, action
group by first_name,last_name
having count(*) >=2 ) p2
on p.first_name=p2.first_name and p.last_name=p2.last_name

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use a derived table to get the persons having atleast 2 distinct actions and join it to the original table to get the other columns in the result.

select p.*
from person_log p 
join (select first_name,last_name
      from person_log
      group by first_name,last_name
      having count(*) >=2 and count(distinct action) >= 2) p1
on p1.first_name=p.first_name and p1.last_name=p.last_name

Upvotes: 1

Related Questions