Order by with group by, different results from different versions of mysql server

lagu2653 · August 26, 2011, 3:04pm

I have locally on my PC

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.1.47-community             |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | ia32                         |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+

the server version is:

Serverversion: 5.1.55
Protokollversion: 10
UTF-8 Unicode (utf8)

The thread below solved my problem locally, but from the production server I get only 11 rows. Locally I get 32. If I check the data in both databases with another query. I get all 36 rows on both servers as I should.
MySQL ORDER BY with GROUP BY PHP Server Side Scripting forum at WebmasterWorld
This is my query:

SELECT publisher_keyword_bids . *
FROM publisher_keyword_bids
INNER JOIN (SELECT MAX( publisher_keyword_bid_id ) AS id
FROM publisher_keyword_bids
GROUP BY ad_group_keyword_id
)ids ON publisher_keyword_bids.publisher_keyword_bid_id = ids.id
WHERE `publisher_id` =79
AND `created`
BETWEEN '2011-08-08 07:00:00'
AND '2011-08-08 13:00:00'
ORDER BY `created` DESC;

How do I get 32 rows from Serverversion: 5.1.55?

r937 · August 26, 2011, 4:24pm

could you do a SHOW CREATE TABLE please

i’ll assume you have ensured that the table structures are identical

you say you’ve run queries to ensure the same rows in both servers? are the rows identical in all columns?

lagu2653 · August 26, 2011, 8:29pm

locally:

CREATE TABLE `publisher_keyword_bids` (
  `publisher_keyword_bid_id` int(10) NOT NULL AUTO_INCREMENT,
  `ad_group_id` int(10) unsigned NOT NULL,
  `publisher_id` tinyint(3) unsigned NOT NULL,
  `ad_group_keyword_id` int(10) unsigned NOT NULL,
  `max_cpc` mediumint(9) unsigned NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`publisher_keyword_bid_id`),
  KEY `INDEX_AD_GROUP_ID_PUBLISHER_ID_AD_GROUP_KEYWORD_ID` (`ad_group_id`,`publisher_id`,`ad_group_keyword_id`),
  CONSTRAINT `FK_PUBLISHER_KEYWORD_BIDS_PUBLISHER_KEYWORDS` FOREIGN KEY (`ad_group_id`, `publisher_id`,
`ad_group_keyword_id`) REFERENCES `publisher_keywords` (`ad_group_id`, `publisher_id`, `ad_group_keyword_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=462884 DEFAULT CHARSET=utf8 |

server:

CREATE TABLE `publisher_keyword_bids` (
 `publisher_keyword_bid_id` int(10) NOT NULL AUTO_INCREMENT,
 `ad_group_id` int(10) unsigned NOT NULL,
 `publisher_id` tinyint(3) unsigned NOT NULL,
 `ad_group_keyword_id` int(10) unsigned NOT NULL,
 `max_cpc` mediumint(9) unsigned NOT NULL,
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`publisher_keyword_bid_id`),
 KEY `INDEX_AD_GROUP_ID_PUBLISHER_ID_AD_GROUP_KEYWORD_ID` (`ad_group_id`,`publisher_id`,`ad_group_keyword_id`),
 CONSTRAINT `FK_PUBLISHER_KEYWORD_BIDS_PUBLISHER_KEYWORDS` FOREIGN KEY (`ad_group_id`, `publisher_id`, `ad_group_keyword_id`) REFERENCES `publisher_keywords` (`ad_group_id`, `publisher_id`, `ad_group_keyword_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=473918 DEFAULT CHARSET=utf8

I use this query to se that all 36 rows exists in both servers:

SELECT * FROM `publisher_keyword_bids` WHERE `publisher_id` = 79 AND `created` &gt; '2011-08-08 07:00:00' AND `created` &lt; '2011-08-08 13:00:00' order by created desc;

From PhpMyAdmin at the production server:
Ändra == Edit Radera == Delete
(36 rows)

publisher_keyword_bid_id 	ad_group_id 	publisher_id 	ad_group_keyword_id 	max_cpc 	created 
	Ändra 	Radera 	453807 	35299 	79 	3626909 	1400 	2011-08-08 12:41:56
	Ändra 	Radera 	453806 	35299 	79 	3626924 	1200 	2011-08-08 12:41:19
	Ändra 	Radera 	453805 	35299 	79 	3626908 	1200 	2011-08-08 12:41:00
	Ändra 	Radera 	453804 	35299 	79 	3626906 	1000 	2011-08-08 12:40:12
	Ändra 	Radera 	453803 	35299 	79 	3626904 	1600 	2011-08-08 12:39:51
	Ändra 	Radera 	453802 	35345 	79 	3630400 	2000 	2011-08-08 12:37:20
	Ändra 	Radera 	453801 	35345 	79 	3630407 	1600 	2011-08-08 12:36:57
	Ändra 	Radera 	453800 	35345 	79 	3630417 	1500 	2011-08-08 12:36:15
	Ändra 	Radera 	453799 	35345 	79 	3630402 	2000 	2011-08-08 12:36:02
	Ändra 	Radera 	453798 	35298 	79 	3626383 	2400 	2011-08-08 12:35:21
	Ändra 	Radera 	453797 	35298 	79 	3626368 	2400 	2011-08-08 12:34:41
	Ändra 	Radera 	453796 	35298 	79 	3626367 	2400 	2011-08-08 12:33:05
	Ändra 	Radera 	453795 	35298 	79 	3626522 	1500 	2011-08-08 12:32:34
	Ändra 	Radera 	453794 	35298 	79 	3626368 	2000 	2011-08-08 12:31:43
	Ändra 	Radera 	453793 	35298 	79 	3626367 	2000 	2011-08-08 12:31:11
	Ändra 	Radera 	453792 	35298 	79 	3626367 	2000 	2011-08-08 12:31:03
	Ändra 	Radera 	453791 	35292 	79 	3623696 	900 	2011-08-08 12:22:34
	Ändra 	Radera 	453790 	35292 	79 	3623749 	1500 	2011-08-08 12:22:11
	Ändra 	Radera 	453789 	35292 	79 	3623959 	1400 	2011-08-08 12:21:16
	Ändra 	Radera 	453788 	35292 	79 	3623913 	1200 	2011-08-08 12:21:07
	Ändra 	Radera 	453787 	35292 	79 	3624830 	1400 	2011-08-08 12:19:15
	Ändra 	Radera 	453786 	35292 	79 	3623917 	1500 	2011-08-08 12:18:56
	Ändra 	Radera 	453785 	35292 	79 	3623668 	1100 	2011-08-08 12:18:46
	Ändra 	Radera 	453784 	35292 	79 	3623679 	1300 	2011-08-08 12:18:11
	Ändra 	Radera 	453783 	35292 	79 	3623680 	1800 	2011-08-08 12:17:33
	Ändra 	Radera 	453782 	35295 	79 	3625087 	2500 	2011-08-08 12:15:51
	Ändra 	Radera 	453777 	35295 	79 	3625025 	3500 	2011-08-08 12:13:25
	Ändra 	Radera 	453748 	35295 	79 	3625238 	2000 	2011-08-08 12:11:28
	Ändra 	Radera 	453746 	35295 	79 	3625061 	2000 	2011-08-08 12:10:42
	Ändra 	Radera 	453745 	35295 	79 	3625039 	2500 	2011-08-08 12:08:32

From my local PC (a dump of the production server):
(36 rows)

453807	35299	79	3626909	1400	2011-08-08 12:41:56
453806	35299	79	3626924	1200	2011-08-08 12:41:19
453805	35299	79	3626908	1200	2011-08-08 12:41:00
453804	35299	79	3626906	1000	2011-08-08 12:40:12
453803	35299	79	3626904	1600	2011-08-08 12:39:51
453802	35345	79	3630400	2000	2011-08-08 12:37:20
453801	35345	79	3630407	1600	2011-08-08 12:36:57
453800	35345	79	3630417	1500	2011-08-08 12:36:15
453799	35345	79	3630402	2000	2011-08-08 12:36:02
453798	35298	79	3626383	2400	2011-08-08 12:35:21
453797	35298	79	3626368	2400	2011-08-08 12:34:41
453796	35298	79	3626367	2400	2011-08-08 12:33:05
453795	35298	79	3626522	1500	2011-08-08 12:32:34
453794	35298	79	3626368	2000	2011-08-08 12:31:43
453793	35298	79	3626367	2000	2011-08-08 12:31:11
453792	35298	79	3626367	2000	2011-08-08 12:31:03
453791	35292	79	3623696	900	2011-08-08 12:22:34
453790	35292	79	3623749	1500	2011-08-08 12:22:11
453789	35292	79	3623959	1400	2011-08-08 12:21:16
453788	35292	79	3623913	1200	2011-08-08 12:21:07
453787	35292	79	3624830	1400	2011-08-08 12:19:15
453786	35292	79	3623917	1500	2011-08-08 12:18:56
453785	35292	79	3623668	1100	2011-08-08 12:18:46
453784	35292	79	3623679	1300	2011-08-08 12:18:11
453783	35292	79	3623680	1800	2011-08-08 12:17:33
453782	35295	79	3625087	2500	2011-08-08 12:15:51
453777	35295	79	3625025	3500	2011-08-08 12:13:25
453748	35295	79	3625238	2000	2011-08-08 12:11:28
453746	35295	79	3625061	2000	2011-08-08 12:10:42
453745	35295	79	3625039	2500	2011-08-08 12:08:32
453744	35295	79	3625029	3000	2011-08-08 12:07:25
453743	35295	79	3625026	3000	2011-08-08 12:07:06
453742	35296	79	3626001	2500	2011-08-08 12:05:58
453741	35296	79	3626002	2500	2011-08-08 12:05:23
453740	35296	79	3625999	1600	2011-08-08 12:00:57
453739	35296	79	3625999	1600	2011-08-08 12:00:55

The 32 desired rows: (locally on my PC)

453807	35299	79	3626909	1400	2011-08-08 12:41:56
453806	35299	79	3626924	1200	2011-08-08 12:41:19
453805	35299	79	3626908	1200	2011-08-08 12:41:00
453804	35299	79	3626906	1000	2011-08-08 12:40:12
453803	35299	79	3626904	1600	2011-08-08 12:39:51
453802	35345	79	3630400	2000	2011-08-08 12:37:20
453801	35345	79	3630407	1600	2011-08-08 12:36:57
453800	35345	79	3630417	1500	2011-08-08 12:36:15
453799	35345	79	3630402	2000	2011-08-08 12:36:02
453798	35298	79	3626383	2400	2011-08-08 12:35:21
453797	35298	79	3626368	2400	2011-08-08 12:34:41
453796	35298	79	3626367	2400	2011-08-08 12:33:05
453795	35298	79	3626522	1500	2011-08-08 12:32:34
453791	35292	79	3623696	900	2011-08-08 12:22:34
453790	35292	79	3623749	1500	2011-08-08 12:22:11
453789	35292	79	3623959	1400	2011-08-08 12:21:16
453788	35292	79	3623913	1200	2011-08-08 12:21:07
453787	35292	79	3624830	1400	2011-08-08 12:19:15
453786	35292	79	3623917	1500	2011-08-08 12:18:56
453785	35292	79	3623668	1100	2011-08-08 12:18:46
453784	35292	79	3623679	1300	2011-08-08 12:18:11
453783	35292	79	3623680	1800	2011-08-08 12:17:33
453782	35295	79	3625087	2500	2011-08-08 12:15:51
453777	35295	79	3625025	3500	2011-08-08 12:13:25
453748	35295	79	3625238	2000	2011-08-08 12:11:28
453746	35295	79	3625061	2000	2011-08-08 12:10:42
453745	35295	79	3625039	2500	2011-08-08 12:08:32
453744	35295	79	3625029	3000	2011-08-08 12:07:25
453743	35295	79	3625026	3000	2011-08-08 12:07:06
453742	35296	79	3626001	2500	2011-08-08 12:05:58
453741	35296	79	3626002	2500	2011-08-08 12:05:23
453740	35296	79	3625999	1600	2011-08-08 12:00:57

The 11 rows at the production server:

	Ändra 	Radera 	453802 	35345 	79 	3630400 	2000 	2011-08-08 12:37:20
	Ändra 	Radera 	453801 	35345 	79 	3630407 	1600 	2011-08-08 12:36:57
	Ändra 	Radera 	453800 	35345 	79 	3630417 	1500 	2011-08-08 12:36:15
	Ändra 	Radera 	453799 	35345 	79 	3630402 	2000 	2011-08-08 12:36:02
	Ändra 	Radera 	453795 	35298 	79 	3626522 	1500 	2011-08-08 12:32:34
	Ändra 	Radera 	453791 	35292 	79 	3623696 	900 	2011-08-08 12:22:34
	Ändra 	Radera 	453748 	35295 	79 	3625238 	2000 	2011-08-08 12:11:28
	Ändra 	Radera 	453742 	35296 	79 	3626001 	2500 	2011-08-08 12:05:58
	Ändra 	Radera 	453741 	35296 	79 	3626002 	2500 	2011-08-08 12:05:23
	Ändra 	Radera 	453740 	35296 	79 	3625999 	1600 	2011-08-08 12:00:57

r937 · August 27, 2011, 11:07am

notice that your data contains these three rows –


453796 	35298 	79 	3626367 	2400 	2011-08-08 12:33:05
453793 	35298 	79 	3626367 	2000 	2011-08-08 12:31:11
453792 	35298 	79 	3626367 	2000 	2011-08-08 12:31:03

the ad_group_keyword_id is 3626367

so the MAX(publisher_keyword_bid_id) for these rows is 453796

therefore the other two rows should ~not~ be produced by the query

therefore the logic of the subqery is faulty

here, try this query instead –

SELECT publisher_keyword_bids.*
  FROM publisher_keyword_bids.
INNER
  JOIN ( SELECT MAX( publisher_keyword_bid_id ) AS id
           FROM publisher_keyword_bids
          [COLOR="Blue"]WHERE publisher_id =79
            AND created 
                BETWEEN '2011-08-08 07:00:00'
                    AND '2011-08-08 13:00:00'[/COLOR]
         GROUP 
             BY ad_group_keyword_id ) ids 
    ON publisher_keyword_bids.publisher_keyword_bid_id = ids.id
 WHERE publisher_keyword_bids.publisher_id =79
   AND publisher_keyword_bids.created 
       BETWEEN '2011-08-08 07:00:00'
           AND '2011-08-08 13:00:00'
ORDER 
    BY publisher_keyword_bids.created DESC

Topic		Replies	Views
[MySql 5.0.45-community-nt] Order by Case When and CHAR_LENGTH Databases	3	640	September 24, 2014
How to code an SQL query with ORDER BY as a variable? Databases	21	5292	June 14, 2023
Order MySql result by number of rows PHP mysql	2	961	September 16, 2021
Possible to order by count somehow? Need help with a mysql query Databases	8	964	October 8, 2014
ORDER BY variable/array within a foreach loop PHP	5	21367	October 16, 2014

Order by with group by, different results from different versions of mysql server

Related topics