Quote
select * from wtl where id between 500001 and 1000000 group by
a_num,b_num,calldate,call_time,duration
I only want to remove duplicates. I want to avoid deletion operation so the result of the select is written into another table.
OK, so you want to remove duplicates records based on a_num, b_num, calldate, calltime and duration. For any given record, which values of all the
other fields (say, id) do you want copied across to your other table?
This is the problem with grouping.
The fields you group by become the [unique] "keys" of the new records, because you'll only have unique combinations of those fields but, where many records "match" those keys, which record should MySQL take the individual values from?
Aggregate functions, like Sum(), roll all of these individual values up into a single item, which is fine but if you
don't use an aggregate function, then MySQL has
no way of knowing which row to use! Officially, this behaviour is "undefined", deeply undesirable and, in more recent versions of MySQL,
not allowed by default.
select *
from table1
order by a, b, c ;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
+---+---+---+
select a, b, c
from table1
group by a, b
order by a, b ;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | ? | <- what should go here?
+---+---+---+
select a, b, max( c ) c
from table1
group by a, b
order by a, b ;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 4 | <- well-defined behaviour
+---+---+---+
Regards, Phill W.