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

-------------Problems Reply------------

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 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.

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

Category:mysql Views:1 Time:2018-11-07
Tags: mysql sql

Related post

  • MySQL Group By to display latest result 2011-01-12

    I'm trying to query MySQL to ORDER then GROUP... it's a question that comes up a lot here and I found an answer that seemed relevant to me: Getting a MySQL group by query to display the row in that group with the highest value However I'm finding tha

  • How to fix a MySQL Group By query? 2011-12-09

    I've got a table that looks like this id, member_name, member_group, flag member_group is a text value like 'admin','moderator','user' flag has a value of 0, 1 or 2. I'm trying to figure out how to produce the following result from this table Let's a

  • MySQL: Limit number of results for each group in query based on results from another query 2012-02-07

    I would like to thank everyone in advance for any help you would share on this following issue. In mysql I have the following tables: select id, product_id from MembersProducts; Member Products id | product_id 1 | 2 1 | 3 1 | 4 1 | 5 1 | 6 2 | 5 2 |

  • MySQL Group by Query but with one query i need to convert Currency Rate & merge Result 2012-03-02

    I have Table with Field id | Customer | Currency Type | Sales Amt | Date ===================================================== 1 | ClientA | Dollar | 100$ | Timestamp 2 | ClientB | Rupees | 1500Rs | Timestamp I am trying query like "Select Currency T

  • mysql group by query 2010-03-09

    I have the following table: Service_ID feedback 31 1 32 1 33 1 I have the following query to find the sum: SELECT Service_ID,SUM(consumer_feedback) FROM consumer5 group by Service_ID I have to get the output as 31 1 32 1 33 1 I am getting it. But for

  • Getting a MySQL group by query to display the row in that group with the highest value 2010-08-28

    I'm trying to figure out how to query my database so that it will essentially first ORDER my results and then GROUP them... This question seems to be slightly common and I have found examples but I still don't quite grasp the 'how' to do this and use

  • MySQL: Group most up-to-date result with the same id_type only 2010-11-07

    I'm trying to make a SELECT with a table which could contain more values with the same id_type like in this example: table_intensity id id_type value user_id creation_date 1 1 90.4 73 2010-11-06 16:27:32 2 4 1258 27 2010-11-06 16:27:48 <= up-to-da

  • MySQL GROUP BY query problem 2011-08-23

    I have the following tables setup in a MySQL database: Client: id name Session: id service_id date Attendance: id client_id session_id Service: id duration Clients have a many to many relationship with Sessions through the Attendance table. I am tryi

  • MYSQL: how to return all distinct row in mysql GROUP BY query 2011-11-16

    I have table T with some column and values T ======================== id | name | g1 | g2 ========================= 10 | abc | 1 | 1 14 | abc | 1 | 0 33 | abc | 1 | 0 42 | def | 1 | 0 52 | def | 1 | 1 63 | def | 2 | 0 66 | def | 2 | 0 67 | def | 2 |

  • Help needed with simple mysql group by query 2010-04-27

    This query fails when I add the line shown... Select Companyid, count(*) as cnt from mytable where State is not null and cnt = 1 <------------------------- FAIL group by CompanyID Any way to do this? Here's a long winded background if it'll help..

  • MYSQL Group together query 2011-03-10

    I have a table with 512 entries in it (initially, this will increase with records at we add to it). This table has columns: id (id is auto_incrementing and primary) ip_address in_use (in_use is bool, true / false) Basically I have a list of addresses

  • Same query produces different results in SQL 2011-05-26

    In one machine, we have a set of data and say we have a column isValid which contains true or false and we also have another column which defines a group.For every group there can be only one value as true for Isvalid column and the rest as false. No

  • mySQL Group By query not working 2011-07-19

    I want to join the tables below. I have query: SELECT MAX(s.Date) AS last_ship_date, s.Form AS summary_form, SUM(s.Total) AS qty_ship, SUM(f.Quantity) AS qty_req FROM Slip AS s LEFT OUTER JOIN Forms AS f ON s.Form=f.Form AND s.DocketNumber=f.DocketNu

  • MySQL Left Join not producing expected results 2011-10-06

    I know there is a lot of good questions answered on Left Joins, but I am having trouble finding something pertaining to my particular problem. I apologize for a possible repeated topic, but help would be greatly appreciated. I have two tables that co

  • PHP, MYSQL + javascript? to produce a result (price) based on three user SELECT inputs 2011-11-04

    I'm very new (one month) into coding and using MySQL, PHP, and (just started last night) Javascript. I am trying to find a code example to do the following: a) User uses a drop list (SELECT) to choose a product (say a hotel). b) Same user also uses a

  • Getting the good row in a MySQL GROUP BY query 2012-02-06

    I have a MySql table named 'comments' : id | date | movie_id | comment_value 1 2011/11/05 10 comment_value_1 2 2012/01/10 10 comment_value_2 3 2011/10/10 15 comment_value_3 4 2011/11/20 15 comment_value_4 5 2011/12/10 30 comment_value_5 And i try to

  • SQL Query Producing No Results 2010-01-15

    I am trying to make this query work: SELECT Stock.*, StockFeatures.Features, StockDescriptions.Detailed, StockDescriptions.Technical, PRD1.RuleValue as Price, PRD2.RuleValue as WasPrice, PRD2.RuleValue-PRD1.RuleValue as Save, PRD1.Quantity FROM Stock

  • MySQL GROUP BY query question 2011-05-06

    I have a table that contains a name of a color (teal, for example) and the associated primary color (blue). Sometimes a color entry can be the same as its associated primary color (red, red). How can I do a GROUP BY PRIMARY_COLOR where the COLOR for

  • MySQL: error when query returns no results? 2011-08-26

    I have a query that looks like this: SELECT number FROM table1 INNER JOIN table2 WHERE name = 'stack_overflow' AND table1.id = table2.id AND user_id = 5 This returns a number. It does the right thing, but when inside name I pass a name that does not

  • Query producing unexpected results (sha1) 2011-11-17

    I have a form for updating user data. It posts to this page: <?php //Update user table session_start(); include 'sql_connect_R.inc.php'; $id = mysql_real_escape_string($_POST['userID']); $password = mysql_real_escape_string($_POST['user_passwrd'])

  • Why does my Linq Where clause produce more results instead of less? 2009-05-06

    I just had the weirdest debug experience in a very long time. It's a bit embarassing to admit, but it lead me to be believe that my Linq query produces MORE results when adding an additional Where clause. I know it's not possible, so I've refactored

  • PDO - query not returning results 2012-02-02

    Having trouble with query results. The getSales() function works great the first time it is called. When called again, the query produces no results. Here is a small chunk of code: abstract class Reporting { protected function connect() { try { $this

  • How do I create a query that caters for two different groups of data and produces a result that is dependent on the first set in MySQL? 2012-04-04

    I'm trying to count all instances where a group of data has one or more fail. I'm also Finding it very difficult to build this question so I'm hoping that showing an example will do the trick in explaining what I'm trying to achieve. Sample data: INS

  • MySQL query order the results in GROUP BY 2011-05-08

    I'm coding a forum system and I'm trying to get the last post in a topic. The problem is I'm grouping the results on the topic id and I can't figure out a way to get the last reply to be displayed in the grouped data. Here is my query so far: SELECT

  • Wordpress/MySql query is not producing all results from the LEFT part of the JOIN 2012-01-24

    I am working in a MySQL database. I have added a custom field to a Wordpress database called "short_url" I want a list of all posts in the database along with the value for "short_url" if the post has that value. But I want all of the posts nonethele

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.155 (s). 11 q(s)