Same exact query. Same exact dataset. Two different MySQL servers. TWO DIFFERENT RESULTS. Why?
SELECT DISTINCT term_taxonomy_id FROM (
SELECT * FROM azEw_term_relationships
WHERE `term_taxonomy_id` IN
(SELECT term_taxonomy_id FROM azEw_term_taxonomy WHERE taxonomy = 'series')
ORDER BY object_id DESC) `series_term_ids`;
My expectation is that if series_term_ids table is sorted, then SELECT DISTINCT will grab the first row for each term_taxonomy_id.
Server 1 mysql Ver 14.14 Distrib 5.6.35, for osx10.9 (x86_64) using EditLine wrapper
returns 29 rows in the desired order:
61424
2221
3529
10404
21993
33
[etc]
Server 2 mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
returns the same 29 rows by SORTED ASC.
21
23
30
33
1013
1027
1042
2221
[etc]
This is part of a WordPress query where I'm trying to get a list of taxonomy terms sorted by most recent post in each term. Using the post_meta object ID is a close enough approximation of the date of the latest post (I'm aware it's actually the order of the most recent addition to that term, but that's close enough and avoids a join to the wp_posts table). The WordPress query is here (totally open to modifying it):
<?php
global $wpdb;
$query = "
SELECT DISTINCT term_taxonomy_id
FROM ( SELECT *
FROM $wpdb->term_relationships
WHERE `term_taxonomy_id` IN
( SELECT term_taxonomy_id
FROM $wpdb->term_taxonomy
WHERE taxonomy = 'series' )
ORDER BY object_id DESC ) `series_term_ids`";
$output = 'ARRAY_A';
$series_array = $wpdb->get_results( $query, $output );
$s_terms = get_terms( array(
'taxonomy' => 'series',
'orderby' => 'include',
'include' => array_column( $series_array, 'term_taxonomy_id' ),
'hide_empty' => 1
) );
?>
My expectation is that if series_term_ids table is sorted, then SELECT DISTINCT will grab the first row for each term_taxonomy_id.
MySQL does not guarantee order in such circumstances.
You're better off being a little more literal and doing something like:
SELECT A
FROM (...) AS theSubQ
GROUP BY A
ORDER BY MAX(B) DESC
;