MySql column without aggregates and group by clause

group byMySQL

Since I am coming from MSSQL world I am confused, what happening in queries where you have group by clause and other columns in select which do not go through aggregates functions.

I was shocked when MySql executed query like this

select * from myTable
group by columnThatIsNotPk

Or fiddle like this
http://sqlfiddle.com/#!9/b30bf5/1

My questions are:

Is there an explanation which data I am going to get for columns that not exists in group by clause?
Is it ok to use queries like that?
Do I lose or gain performances with queries like this?
Does MySql implements First and Last aggregates as it does JetEngine in ACCESS, and unfortunately missing feature in MSSQL engine?

Best Answer

For the question about aggregates: See only_full_group_by, which was added in MySQL 5.7.5. Before that, you got random values for the columns not based on the GROUP BY column(s). After that, the statement is in error (unless you turn off that flag).

As for whether there is a performance gain? Who cares, if you get garbage? SELECT * is probably never faster (other than to type) than SELECT ... with specific columns.

As for FIRST, LAST, and other "Windowing functions", they are available in MariaDB 10.2 and MySQL 8.0 (but not yet ready for prime time). There are workarounds, often using LIMIT 1.

Another thing to learn about if you are moving to MySQL: Learn about AUTO_INCREMENT as a non-drop-in replacement SEQUENCEs.

Related Solutions

Mysql – How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values

UPDATE Now I see your error. The inner query uses aggregation, and takes out sizeRange which is not a column you aggregate on. So you only get "samples" of that column. Strictly speaking, your query is not valid SQL but MySQL allows it given a relaxed sql_mode.

So your query is inherently erroneous. Will see if I can help fix it.

ORIGINAL answer

My guess for you would be to check the value of group_concat_max_len.

It is by default just 1024, though you typically don't really want a limit for that.

The problem might be that you are only getting partial results, where, by chance or by order of evaluation, "S" and "XL" occupy first 1024 characters or more. I see no reason why "M" or "L" would not be there -- the GROUP_CONCAT doesn't do such distinctions.

So, try out:

SET group_concat_max_len := 1000000;

And execute your query again. If this works, make sure to set said param in you MySQL configuration file.

You may find my related post useful.

GROUP BY with OR/AND clause

You can generate a flag column for each group that you need to get:

 CREATE OR REPLACE VIEW vw_messages AS
 SELECT id
      , `from`
      , `to`
      , double_1
      , double_2
      , case when double_1 = double_2 
              and `to` = 0
             then 1
        else 0
        end flag_1
      , case when double_1 = 0
              and double_2 = 0
              and `to` = $id
             then 1
        else 0
        end flag_2
      , case when double_1 = 0
              and double_2 = 0
              and `from` = $id
             then 1
        else 0
        end flag_3
 FROM messages

This way you can later get the rows that accomplish either one or any combinations of groups.

Best Answer

Related Solutions

Mysql – How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values

GROUP BY with OR/AND clause

Related Question