Question 194913 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Why Random Results Coming With SQL Group By Condition?

I have two tables job_report and index_jobreport.

job_report table "id" column is "index_jobreport.jobreport_id".
index_jobreport have DSR_hashkey column. We group the DSR_hashkey column with the main query.

This time query is getting random rows from time to time.

(attached files - 
first_result.png, 
second_result.png,
index_jobreport_have_3907327_and_3907328.png,
job_report_have_3907327_and_3907328.png,
query_result.png(one of time)

drive – https://drive.google.com/drive/folders/1zlclWLQ_ZJRUnjsQ18TPn4Mv10k7b63u?usp=sharing

This is my query,

SELECT
    data.Id,
    indexJob.DSR_hashkey,
    indexJob.EDI_hashkey,
    indexJob.jobreport_id
FROM
    job_report As data 
    Inner JOIN index_jobreport As indexJob ON data.Id = indexJob.jobreport_id 

WHERE
    data.org_code = 'MAGLOGBOG' 
    AND
    data.GB_Code IN ('DEL','JF5','LAX','MAA','ATL','SZX','DE1') 
    AND
    data.country_code IN ('IN','CN','US') 
    AND
    data.GE_Code IN ('FES','FIS') 

GROUP BY
    indexJob.DSR_hashkey

ORDER BY
    data.Id ASC

We need to get always Group by with DSR_hashkey and the need to get its First row. But now My query getting random rows from time to time.

What would be the best possible options?

Tags:

2

Answers


  1. 0

    GROUP BY clause should contain all the columns that are present in the select statement

    Login or Signup to reply.
  2. 0

    You use GROUP BY indexJob.DSR_hashkey. So all rows which contains the same value in this column are treated as one group, and you receive one row for all this group.

    You select four columns: data.Id, indexJob.DSR_hashkey, indexJob.EDI_hashkey, indexJob.jobreport_id. All rows in a group contains the same value in 2nd column. But the values in another columns differs. Query must return only one value. And server doesn’t know what one value from all possible ones it must return.

    So it returns indefinite, randomly got value from all possible ones.

    You tell that you need "first row". And again – you use ORDER BY data.Id ASC. But this column contains a lot of values in a group, and server does not know what value must be used for sorting.. and it uses indefinite value producing undefined sorting.

    Seach the forum for "select first value in a group [mysql]". There is a lot of methods: subquery, window function in CTE, etc. The version of your server is critical – not all methods can be used on some old server versions.

    But you must firstly decide what is the criteria which allows to understand what row in a group is "first".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search