CODEWITHSUNDEEP
mysqlsql
user4337640
user4337640

Reputation:

MySQL Group By query produces indeterminate results

I have a problem ...
I have one request, but each time I'm refresh the request, the result changes.
Here is my request 

  SELECT `t`.*, COUNT('ID') AS `tr` FROM (
  SELECT * FROM HISTORY WHERE USER_ID = 1 AND BIEN_ID IS NOT NULL 
  ORDER BY CREATED_AT DESC) AS `t` WHERE (`USER_ID` = 1) 
  GROUP BY `t`.`BIEN_ID` ORDER BY `t`.`CREATED_AT` DESC

I actually want to group by BIEN_ID with the most recent CREATED_ATfield
Is someone can help me?

For example, first time I got this: 

ID   |USER_ID| BIEN_ID | CREATED_AT  
205  |   1   |     36  | 2015-06-10 17:00:36  
203  |   1   |     48  | 2015-06-10 17:00:2

And after I got this:

ID  | USER_ID| BIEN_ID | CREATED_AT  
202 | 1      | 7       | 2015-06-10 16:31:48  
196 | 1      | 48      | 2015-06-10 16:23:4

Here is my HISTORY DB:

ID  | USER_ID| BIEN_ID | CREATED_AT  
202 | 1      | 7       | 2015-06-10 16:31:48  
199 | 1      | 48      | 2015-06-10 16:23:40   
196 | 1      | 48      | 2015-06-09 16:23:31  
140 | 1      | 7       | 2015-06-09 10:01:20   
134 | 1      | 30      | 2015-06-04 09:10:11
100 | 1      | 48      | 2015-06-01 11:32:40

And here is what I expect (I want to GROUP BY ID, COUNT the same BIEN_ID, and get the most recent CREATED_AT) 

ID  | USER_ID| BIEN_ID | COUNT | CREATED_AT  
202 | 1      | 7       | 2     | 2015-06-10 16:31:48  
199 | 1      | 48      | 3     | 2015-06-10 16:23:40   
134 | 1      | 30      | 1     | 2015-06-04 09:10:11

Upvotes: 2

Views: 238

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You are using an extension to MySQL GROUP BY that is explicitly documented to return indeterminate results. Hence, you should not be surprised that you are getting, well, indeterminate results.

The problem is that you are doing select * in a group by query. The group by column is t.bien_id -- I am guessing from the context that this is not a unique id on each row. (If so, the count would always be 1 and the query would work consistently from run-to-run.)

The specific documentation is:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Upvotes: 5

Nagendra Nigade
Nagendra Nigade

Reputation: 876

select a.id,a.user_id,a.bien_id,a.created_at,b.TOTAL_OCCURANCES from (select *
 from temp1 where created_at in (select MAX(created_at) from temp1 group by
 BIEN_ID))a , (select count(*) as TOTAL_OCCURANCES,BIEN_ID from temp1 group by
 BIEN_ID) b where a.BIEN_ID=b.BIEN_ID order by CREATED_AT desc;

This will perfectly work as you expected.

Fiddle : http://www.sqlfiddle.com/#!9/f3e43/22/3

Upvotes: 1

Related Questions