CODEWITHSUNDEEP
mysqlsql
navjot singh
navjot singh

Reputation: 143

Fetching unique records from duplicate data without using GROUP BY

I have some problem with GROUP BY. For some reason I cannot use it. So I am looking for some alternative solution. I am using online webhosting and with GROUP BY I am getting this error:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'denieuwe_db.guest.gid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Bit Intro: I am making a restaurant website and in the Guest table I have all the guest_code which is unique. Inside the purchase table there are all the purchases of the guest with purchaseid & guest_code

Table: Purchase

+------------------------+
|purchaseid | guest_code | 
+------------------------+
|    1      | CUST7408668|   
|    2      | CUST7408668|    
|    3      | CUST4425874| 
|    4      | CUST4425874|   
|    5      | CUST4425874|  
+------------------------+

Table: guest

    +-----------------------------------+
    |   gid     | guest_code |   Name   |
    +-----------------------------------+
    |    1      | CUST7408668|    Mia   |     
    |    5      | CUST4425874|    zoi   |
    +------------------------+----------+

SQL: 

     SELECT purchase.purchaseid,
            purchase.guest_code, 
            guest.guest_code,
            guest.name,    
     FROM purchase
     INNER JOIN guest ON purchase.guest_code=guest.guest_code GROUP BY purchase.guest_code;

What I want in result:

+-----------------------------------+--------------------+
|purchaseid | guest_code |   name   |  gid  | guest_code | 
+-----------------------------------+--------------------+
|    1      | CUST7408668|    Mia   |   1   | CUST7408668|
|    3      | CUST4425874|    zoi   |   5   | CUST4425874|
+------------------------+----------+--------------------+

I am getting exact result using GROUP BY on localhost but getting a problem on my hosting with it. So I am looking for alternative solution. Any help is appreciated

Upvotes: 0

Views: 56

Answers (5)

navjot singh
navjot singh

Reputation: 143

QUESTION UPDATE

I contacted the Webhost technical team and this is what they say about this issue.

Hi,

on shared hosting this is not allowed as you change the database server behavior for ALL other websites.

But you can tweak your SQL queries to get a similar behavior by using ANY_VALUE() for each nonaggregated column.

eg.

SELECT name, address, MAX(age) FROM t GROUP BY name;

fails, because address is not in the GROUP BY.

using:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

works on the other side and produces similar result to disabling ONLY_FULL_GROUP_BY

Explanation from MySQL:

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses. Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group.

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Aggregate before joining:

 SELECT p.purchaseid, p.guest_code, g.name,    
 FROM (SELECT MIN(p.purchaseid) as purchaseid, p.guest_code
       FROM purchase p
       GROUP BY p.guest_code
      ) p JOIN
      guest g
      ON p.guest_code = g.guest_code;

Upvotes: 1

Adarsh
Adarsh

Reputation: 726

may be one simplest way, using only guest_code in the group by because its related.

  SELECT MIN(purchase.purchaseid) AS purchaseid,
     purchase.guest_code, 
     guest.Name,
     guest.gid,  
     guest.guest_code
  FROM purchase
  INNER JOIN guest 
  ON purchase.guest_code=guest.guest_code 
  GROUP BY purchase.guest_code
  ORDER BY 1;

Upvotes: 1

Hemanth B
Hemanth B

Reputation: 367

You can use Distinct on purchase table guest code to remove duplicate data, since you are not doing any aggregation

SELECT DISTINCT( purchase.guest_code ), 
               purchase.purchaseid, 
               guest.guest_code, 
               guest.NAME, 
FROM   purchase 
       INNER JOIN guest 
               ON purchase.guest_code = guest.guest_code

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

It seems like you want the minimum purchase ID. You can use min() for that. You might also need to extend the GROUP BY clause with the other columns.

SELECT min(purchase.purchaseid) purchaseid,
       purchase.guest_code,
       guest.guest_code,
       guest.name
       FROM purchase
            INNER JOIN guest
                       ON purchase.guest_code = guest.guest_code
       GROUP BY purchase.guest_code,
                guest.guest_code,
                guest.name;

Upvotes: 1

Related Questions