Group by, Order by worked in MySQL but not MariaDB

We managed awhile back to cobble some really complex (to us) database queries that pull some dynamic meta_values in with the general Post info in WordPress so that the result was a nice, full list of key=>value pairs. It's worked great for years but now we've switched to MariaDB and it's no longer ordering properly and I'm not sure why. See below for one of the big, long examples or screenshot at https://screencast.com/t/mFILYpWf for a bit more formatting.

Any ideas on how I can get it to order by menu_order first, last_name then first_name?

Thanks as always!!

SELECT *

FROM

(

    SELECT wp_posts.ID, wp_posts.menu_order, wp_posts.post_title,

        wp_posts.post_name,

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_job_title'

                 THEN wp_postmeta.meta_value END ) AS 'job_title',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_prefix'

                 THEN wp_postmeta.meta_value END ) AS 'prefix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_first_name'

                 THEN wp_postmeta.meta_value END ) AS 'first_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_middle_name'

                 THEN wp_postmeta.meta_value END ) AS 'middle_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_last_name'

                 THEN wp_postmeta.meta_value END ) AS 'last_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_suffix'

                 THEN wp_postmeta.meta_value END ) AS 'suffix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_languages'

                 THEN wp_postmeta.meta_value END ) AS 'languages',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_email'

                 THEN wp_postmeta.meta_value END ) AS 'email',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_phone'

                 THEN wp_postmeta.meta_value END ) AS 'phone',

        MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_offices' THEN wp_postmeta.meta_value END ) AS 'offices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_linkedin' THEN wp_postmeta.meta_value END ) AS 'linkedin',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_biography' THEN wp_postmeta.meta_value END ) AS 'biography',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_credentials' THEN wp_postmeta.meta_value END ) AS 'bio_field_credentials',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_affiliations' THEN wp_postmeta.meta_value END ) AS 'bio_field_affiliations',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_accolades' THEN wp_postmeta.meta_value END ) AS 'bio_field_accolades',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_notable_decisions' THEN wp_postmeta.meta_value END ) AS 'bio_field_notable_decisions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_education_admissions' THEN wp_postmeta.meta_value END ) AS 'bio_field_education_admissions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_image' THEN wp_postmeta.meta_value END ) AS 'bio_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_directory_image' THEN wp_postmeta.meta_value END ) AS 'directory_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_pdf_image' THEN wp_postmeta.meta_value END ) AS 'pdf_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_practices' THEN wp_postmeta.meta_value END ) AS 'practices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_posts' THEN wp_postmeta.meta_value END ) AS 'related_posts',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_other_news' THEN wp_postmeta.meta_value END ) AS 'other_news',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_staff' THEN wp_postmeta.meta_value END ) AS 'related_staff', concat( 'http://wiedner.localhost/lawyer/', wp_posts.post_name, '/' ) AS 'permalink' FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'lawyer'

    GROUP BY wp_posts.ID

    ORDER BY menu_order asc, last_name asc, first_name asc

) AS t

WHERE 1=1

edited Nov 20 '18 at 1:07

Tim Biegeleisen

220k1388141

asked Nov 20 '18 at 1:04

Philip Light

205

1

What is the current error message? Your GROUP BY may even work in strict mode, assuming that wp_posts.ID functionally determines the other columns you are selecting in your subquery. Also, why does ORDER BY appear inside the subquery? It should be on the outside.

– Tim Biegeleisen
Nov 20 '18 at 1:09

add a comment |

Any ideas on how I can get it to order by menu_order first, last_name then first_name?

Thanks as always!!

SELECT *

FROM

(

    SELECT wp_posts.ID, wp_posts.menu_order, wp_posts.post_title,

        wp_posts.post_name,

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_job_title'

                 THEN wp_postmeta.meta_value END ) AS 'job_title',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_prefix'

                 THEN wp_postmeta.meta_value END ) AS 'prefix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_first_name'

                 THEN wp_postmeta.meta_value END ) AS 'first_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_middle_name'

                 THEN wp_postmeta.meta_value END ) AS 'middle_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_last_name'

                 THEN wp_postmeta.meta_value END ) AS 'last_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_suffix'

                 THEN wp_postmeta.meta_value END ) AS 'suffix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_languages'

                 THEN wp_postmeta.meta_value END ) AS 'languages',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_email'

                 THEN wp_postmeta.meta_value END ) AS 'email',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_phone'

                 THEN wp_postmeta.meta_value END ) AS 'phone',

        MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_offices' THEN wp_postmeta.meta_value END ) AS 'offices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_linkedin' THEN wp_postmeta.meta_value END ) AS 'linkedin',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_biography' THEN wp_postmeta.meta_value END ) AS 'biography',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_credentials' THEN wp_postmeta.meta_value END ) AS 'bio_field_credentials',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_affiliations' THEN wp_postmeta.meta_value END ) AS 'bio_field_affiliations',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_accolades' THEN wp_postmeta.meta_value END ) AS 'bio_field_accolades',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_notable_decisions' THEN wp_postmeta.meta_value END ) AS 'bio_field_notable_decisions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_education_admissions' THEN wp_postmeta.meta_value END ) AS 'bio_field_education_admissions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_image' THEN wp_postmeta.meta_value END ) AS 'bio_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_directory_image' THEN wp_postmeta.meta_value END ) AS 'directory_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_pdf_image' THEN wp_postmeta.meta_value END ) AS 'pdf_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_practices' THEN wp_postmeta.meta_value END ) AS 'practices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_posts' THEN wp_postmeta.meta_value END ) AS 'related_posts',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_other_news' THEN wp_postmeta.meta_value END ) AS 'other_news',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_staff' THEN wp_postmeta.meta_value END ) AS 'related_staff', concat( 'http://wiedner.localhost/lawyer/', wp_posts.post_name, '/' ) AS 'permalink' FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'lawyer'

    GROUP BY wp_posts.ID

    ORDER BY menu_order asc, last_name asc, first_name asc

) AS t

WHERE 1=1

edited Nov 20 '18 at 1:07

Tim Biegeleisen

220k1388141

asked Nov 20 '18 at 1:04

Philip Light

205

1

What is the current error message? Your GROUP BY may even work in strict mode, assuming that wp_posts.ID functionally determines the other columns you are selecting in your subquery. Also, why does ORDER BY appear inside the subquery? It should be on the outside.

– Tim Biegeleisen
Nov 20 '18 at 1:09

add a comment |

Any ideas on how I can get it to order by menu_order first, last_name then first_name?

Thanks as always!!

SELECT *

FROM

(

    SELECT wp_posts.ID, wp_posts.menu_order, wp_posts.post_title,

        wp_posts.post_name,

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_job_title'

                 THEN wp_postmeta.meta_value END ) AS 'job_title',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_prefix'

                 THEN wp_postmeta.meta_value END ) AS 'prefix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_first_name'

                 THEN wp_postmeta.meta_value END ) AS 'first_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_middle_name'

                 THEN wp_postmeta.meta_value END ) AS 'middle_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_last_name'

                 THEN wp_postmeta.meta_value END ) AS 'last_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_suffix'

                 THEN wp_postmeta.meta_value END ) AS 'suffix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_languages'

                 THEN wp_postmeta.meta_value END ) AS 'languages',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_email'

                 THEN wp_postmeta.meta_value END ) AS 'email',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_phone'

                 THEN wp_postmeta.meta_value END ) AS 'phone',

        MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_offices' THEN wp_postmeta.meta_value END ) AS 'offices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_linkedin' THEN wp_postmeta.meta_value END ) AS 'linkedin',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_biography' THEN wp_postmeta.meta_value END ) AS 'biography',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_credentials' THEN wp_postmeta.meta_value END ) AS 'bio_field_credentials',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_affiliations' THEN wp_postmeta.meta_value END ) AS 'bio_field_affiliations',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_accolades' THEN wp_postmeta.meta_value END ) AS 'bio_field_accolades',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_notable_decisions' THEN wp_postmeta.meta_value END ) AS 'bio_field_notable_decisions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_education_admissions' THEN wp_postmeta.meta_value END ) AS 'bio_field_education_admissions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_image' THEN wp_postmeta.meta_value END ) AS 'bio_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_directory_image' THEN wp_postmeta.meta_value END ) AS 'directory_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_pdf_image' THEN wp_postmeta.meta_value END ) AS 'pdf_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_practices' THEN wp_postmeta.meta_value END ) AS 'practices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_posts' THEN wp_postmeta.meta_value END ) AS 'related_posts',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_other_news' THEN wp_postmeta.meta_value END ) AS 'other_news',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_staff' THEN wp_postmeta.meta_value END ) AS 'related_staff', concat( 'http://wiedner.localhost/lawyer/', wp_posts.post_name, '/' ) AS 'permalink' FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'lawyer'

    GROUP BY wp_posts.ID

    ORDER BY menu_order asc, last_name asc, first_name asc

) AS t

WHERE 1=1

edited Nov 20 '18 at 1:07

Tim Biegeleisen

220k1388141

asked Nov 20 '18 at 1:04

Philip Light

205

Any ideas on how I can get it to order by menu_order first, last_name then first_name?

Thanks as always!!

SELECT *

FROM

(

    SELECT wp_posts.ID, wp_posts.menu_order, wp_posts.post_title,

        wp_posts.post_name,

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_job_title'

                 THEN wp_postmeta.meta_value END ) AS 'job_title',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_prefix'

                 THEN wp_postmeta.meta_value END ) AS 'prefix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_first_name'

                 THEN wp_postmeta.meta_value END ) AS 'first_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_middle_name'

                 THEN wp_postmeta.meta_value END ) AS 'middle_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_last_name'

                 THEN wp_postmeta.meta_value END ) AS 'last_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_suffix'

                 THEN wp_postmeta.meta_value END ) AS 'suffix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_languages'

                 THEN wp_postmeta.meta_value END ) AS 'languages',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_email'

                 THEN wp_postmeta.meta_value END ) AS 'email',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_phone'

                 THEN wp_postmeta.meta_value END ) AS 'phone',

        MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_offices' THEN wp_postmeta.meta_value END ) AS 'offices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_linkedin' THEN wp_postmeta.meta_value END ) AS 'linkedin',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_biography' THEN wp_postmeta.meta_value END ) AS 'biography',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_credentials' THEN wp_postmeta.meta_value END ) AS 'bio_field_credentials',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_affiliations' THEN wp_postmeta.meta_value END ) AS 'bio_field_affiliations',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_accolades' THEN wp_postmeta.meta_value END ) AS 'bio_field_accolades',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_notable_decisions' THEN wp_postmeta.meta_value END ) AS 'bio_field_notable_decisions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_education_admissions' THEN wp_postmeta.meta_value END ) AS 'bio_field_education_admissions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_image' THEN wp_postmeta.meta_value END ) AS 'bio_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_directory_image' THEN wp_postmeta.meta_value END ) AS 'directory_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_pdf_image' THEN wp_postmeta.meta_value END ) AS 'pdf_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_practices' THEN wp_postmeta.meta_value END ) AS 'practices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_posts' THEN wp_postmeta.meta_value END ) AS 'related_posts',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_other_news' THEN wp_postmeta.meta_value END ) AS 'other_news',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_staff' THEN wp_postmeta.meta_value END ) AS 'related_staff', concat( 'http://wiedner.localhost/lawyer/', wp_posts.post_name, '/' ) AS 'permalink' FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'lawyer'

    GROUP BY wp_posts.ID

    ORDER BY menu_order asc, last_name asc, first_name asc

) AS t

WHERE 1=1

wordpress mariadb

edited Nov 20 '18 at 1:07

Tim Biegeleisen

220k1388141

asked Nov 20 '18 at 1:04

Philip Light

205

edited Nov 20 '18 at 1:07

Tim Biegeleisen

220k1388141

asked Nov 20 '18 at 1:04

Philip Light

205

edited Nov 20 '18 at 1:07

Tim Biegeleisen

220k1388141

edited Nov 20 '18 at 1:07

Tim Biegeleisen

220k1388141

edited Nov 20 '18 at 1:07

Tim Biegeleisen

220k1388141

asked Nov 20 '18 at 1:04

Philip Light

205

asked Nov 20 '18 at 1:04

Philip Light

205

asked Nov 20 '18 at 1:04

Philip Light

205

1

What is the current error message? Your GROUP BY may even work in strict mode, assuming that wp_posts.ID functionally determines the other columns you are selecting in your subquery. Also, why does ORDER BY appear inside the subquery? It should be on the outside.

– Tim Biegeleisen
Nov 20 '18 at 1:09

add a comment |

1

What is the current error message? Your GROUP BY may even work in strict mode, assuming that wp_posts.ID functionally determines the other columns you are selecting in your subquery. Also, why does ORDER BY appear inside the subquery? It should be on the outside.

– Tim Biegeleisen
Nov 20 '18 at 1:09

What is the current error message? Your GROUP BY may even work in strict mode, assuming that wp_posts.ID functionally determines the other columns you are selecting in your subquery. Also, why does ORDER BY appear inside the subquery? It should be on the outside.

– Tim Biegeleisen
Nov 20 '18 at 1:09

add a comment |

2 Answers
2

active

oldest

votes

Simplify it by getting rid of the outer SELECT!

The reason is that a 'derived' table (your subquery) has no order. (This has long been in Standard SQL, but only recently have MariaDB, and later MySQL, taken advantage of it.) That is, the execution is justified to get rid of your carefully written ORDER BY.

But, by stopping with the inner query; it is obligated to honor the ORDER BY.

edited Nov 20 '18 at 3:58

answered Nov 20 '18 at 3:52

Rick James

66.6k55899

Thanks (and Tim) that was it! There was no error FYI just wasn't ordering.

– Philip Light
Nov 20 '18 at 14:32

add a comment |

MariaDB may have a different mode set. Try this, and see here:

 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',));

Also this may be a duplicate, see this related question.

answered Nov 20 '18 at 1:22

Richard Zack

1668

If the Optimizer is smart enough, it will see no violation of the "only full...". And I am pretty sure this is unrelated to why it is "now failing".

– Rick James
Nov 20 '18 at 3:54

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53384790%2fgroup-by-order-by-worked-in-mysql-but-not-mariadb%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

Simplify it by getting rid of the outer SELECT!

But, by stopping with the inner query; it is obligated to honor the ORDER BY.

edited Nov 20 '18 at 3:58

answered Nov 20 '18 at 3:52

Rick James

66.6k55899

Thanks (and Tim) that was it! There was no error FYI just wasn't ordering.

– Philip Light
Nov 20 '18 at 14:32

add a comment |

Simplify it by getting rid of the outer SELECT!

But, by stopping with the inner query; it is obligated to honor the ORDER BY.

edited Nov 20 '18 at 3:58

answered Nov 20 '18 at 3:52

Rick James

66.6k55899

Thanks (and Tim) that was it! There was no error FYI just wasn't ordering.

– Philip Light
Nov 20 '18 at 14:32

add a comment |

Simplify it by getting rid of the outer SELECT!

But, by stopping with the inner query; it is obligated to honor the ORDER BY.

edited Nov 20 '18 at 3:58

answered Nov 20 '18 at 3:52

Rick James

66.6k55899

Simplify it by getting rid of the outer SELECT!

But, by stopping with the inner query; it is obligated to honor the ORDER BY.

edited Nov 20 '18 at 3:58

answered Nov 20 '18 at 3:52

Rick James

66.6k55899

edited Nov 20 '18 at 3:58

answered Nov 20 '18 at 3:52

Rick James

66.6k55899

answered Nov 20 '18 at 3:52

Rick James

66.6k55899

answered Nov 20 '18 at 3:52

Rick James

66.6k55899

Thanks (and Tim) that was it! There was no error FYI just wasn't ordering.

– Philip Light
Nov 20 '18 at 14:32

add a comment |

Thanks (and Tim) that was it! There was no error FYI just wasn't ordering.

– Philip Light
Nov 20 '18 at 14:32

Thanks (and Tim) that was it! There was no error FYI just wasn't ordering.

– Philip Light
Nov 20 '18 at 14:32

add a comment |

MariaDB may have a different mode set. Try this, and see here:

 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',));

Also this may be a duplicate, see this related question.

answered Nov 20 '18 at 1:22

Richard Zack

1668

If the Optimizer is smart enough, it will see no violation of the "only full...". And I am pretty sure this is unrelated to why it is "now failing".

– Rick James
Nov 20 '18 at 3:54

add a comment |

MariaDB may have a different mode set. Try this, and see here:

 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',));

Also this may be a duplicate, see this related question.

answered Nov 20 '18 at 1:22

Richard Zack

1668

If the Optimizer is smart enough, it will see no violation of the "only full...". And I am pretty sure this is unrelated to why it is "now failing".

– Rick James
Nov 20 '18 at 3:54

add a comment |

MariaDB may have a different mode set. Try this, and see here:

 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',));

Also this may be a duplicate, see this related question.

answered Nov 20 '18 at 1:22

Richard Zack

1668

MariaDB may have a different mode set. Try this, and see here:

 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',));

Also this may be a duplicate, see this related question.

answered Nov 20 '18 at 1:22

Richard Zack

1668

answered Nov 20 '18 at 1:22

Richard Zack

1668

answered Nov 20 '18 at 1:22

Richard Zack

1668

answered Nov 20 '18 at 1:22

Richard Zack

1668

If the Optimizer is smart enough, it will see no violation of the "only full...". And I am pretty sure this is unrelated to why it is "now failing".

– Rick James
Nov 20 '18 at 3:54

add a comment |

If the Optimizer is smart enough, it will see no violation of the "only full...". And I am pretty sure this is unrelated to why it is "now failing".

– Rick James
Nov 20 '18 at 3:54

If the Optimizer is smart enough, it will see no violation of the "only full...". And I am pretty sure this is unrelated to why it is "now failing".

– Rick James
Nov 20 '18 at 3:54

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu