Finding differences of column values

Question

Using group by, having clause will let me know if there are more than one record for a given id. Is there anyway to know how these 2 records differ with each other in rest of the columns?

mysql>select pid, name, city from table1;
+------+-------------+--------+
| pid  | name        | city   |
+------+-------------+--------+
|    1 | aa          | delhi  | 
|    2 | bb          | delhi  | 
|    3 | cc          | mumbai | 
|    4 | salman      | pune   | 
|    4 | salman khan | pune   | 
+------+-------------+--------+
5 rows in set (0.00 sec)

mysql>select pid, count(*) as cnt from table1 group by pid having cnt > 1;
+------+-----+
| pid  | cnt |
+------+-----+
|    4 |   2 | 
+------+-----+
1 row in set (0.00 sec)

Expected result:

+------+-------------+
| pid  | name        |
+------+-------------+
|    4 | salman      | 
|    4 | salman khan | 
+------+-------------+
2 rows in set (0.00 sec)

I can achive this by using the following query...

mysql>select pid, name from table1 where pid=4;

But how will I know that these two rows differ in name and the city is the same? There is a timestamp column in the table and I need to order these rows based on that time. Earlist record for a given PID will be the first.

Answer 1

To get the expected result you posted, try:

select pid, name 
from table1 
where pid in 
  (select pid
  from table1 
  group by pid 
  having count(*) > 1)
group by pid, name

If you are especially interested in cases where

• The city and the pid are the same
• The name is different
• Sorted by time within each group

as you explain in your question, try:

select pid, name, city, timestamp 
from table1 
where pid in 
  (select pid
  from table1 
  group by pid, city 
  having count(*) > 1)
group by pid, name, city
order by pid, city, timestamp