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
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
This is too long for a comment.
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 BYso that the select list can refer to nonaggregated columns not named in the
GROUP BYclause. 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 BYare 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.
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