MySQL Forums
Forum List  »  Newbie

Re: GROUP BY behaving strangely
Posted by: Phillip Ward
Date: April 18, 2019 05:34AM

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.

Options: ReplyQuote


Subject
Written By
Posted
April 17, 2019 12:23AM
Re: GROUP BY behaving strangely
April 18, 2019 05:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.