CODEWITHSUNDEEP
mysqlsql
nifelvind
nifelvind

Reputation: 25

How Group By works with Duplicates

I am trying to get some insight as to how some SQL statements work. Right know I am looking into GROUP BY and want to know, how does it choose what to show/return with duplicate data.

Consider the following example:

CREATE TABLE customers 
( 
    FirstName  VARCHAR(50), 
    LastName   VARCHAR(50), 
    MobileNo   VARCHAR(15) 
); 

INSERT INTO customers VALUES ('Niraj','Yadav',989898); 
INSERT INTO customers VALUES ('Chetan','Gadodia',959595); 
INSERT INTO customers VALUES ('Chetan','Gadodia',959590); 
INSERT INTO customers VALUES ('Atul','Kokam',42424242); 
INSERT INTO customers VALUES ('Atul','Kokam',42424246); 
INSERT INTO customers VALUES ('Vishal','Parte',9394452); 
INSERT INTO customers VALUES ('Vishal','Parte',939445); 
INSERT INTO customers VALUES ('Vishal','Parte',9394451); 
INSERT INTO customers VALUES ('Jinendra','Jain',12121); 
INSERT INTO customers VALUES ('Jinendra','Jain',121212);

If I run this query...

SELECT    *
FROM      customers
GROUP BY  FirstName;

I get the following results:

FirstName  LastName  MobileNo  
---------  --------  ----------
Atul       Kokam     42424242  
Chetan     Gadodia   959595    
Jinendra   Jain      12121     
Niraj      Yadav     989898    
Vishal     Parte     9394452

So, my question is: is there any reason why it returns these particular records? How does it determine what to get? I'm using MySQL.

Upvotes: 0

Views: 44

Answers (1)

sstan
sstan

Reputation: 36483

In other databases, your query would not be allowed exactly because the results are unpredictable in this case.

Notice what the MySQL documentation has to say for this case:

MySQL Handling of GROUP BY

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

I should also mention, that Gordon Linoff recently pointed out to me that, starting in version 5.7 of MySQL, a query like yours, where unpredictable results are possible, will no longer be allowed by default.

Info on that: MySQL 5.7: only_full_group_by Improved, Recognizing Functional Dependencies, Enabled by Default!

Upvotes: 4

Related Questions