Geek asks and answers

Finding column differences

Using the group by, having command will let me know if there is more than one record for this identifier. Do I need to know how these 2 records differ from each other in the 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 achieve this using the following query ...

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

But how do I know that these two lines 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 this time. The list entry for the selected PID will be the first.

+3
source share
2 answers

To get the expected result, you sent a message:

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 particularly interested in cases where

  • City and pid are the same
  • The name is different
  • Sort by time within each group

, :

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
+1

googling Kodyaz.com, .

0

More articles:


All Articles