MySQL JOIN and ORDER problem (sorting over multiple tables)_问答_开发者

I have a big problem. I need to join three tables and sort data via date column. I've tried Sub-Selects, Unions, and Joins - no success.

portal_gm_provide is the table where the global wms_id is stored.
portal_gm_log is the table where all logging information (info, warning, error) is stored.
portal_gm_cron is the table where the cronjob information is stored (success or error).

The problem is that these three tables haven't the same structure nor the same date field. Can I join and order the data that every data that fit is in a row and unneeded fields are NULL like in a LEFT/RIGHT join?

SELECT
db_main.wms_id AS id_wms,
db_log.message, db_log.message_real, db_log.id_layer,
db_log.code, db_log.subcode, db_log.counter, db_log.time_first, db_log.time_last,
db_cron.error, db_cron.errorno,
CASE
    WHEN db_log.time_first IS NULL THEN db_cron.time
    ELSE NULL # db_log.time_first
END AS time
FROM portal_gm_provide AS db_main
RIGHT JOIN portal_gm_log AS db_log ON db_log.id_wms = db_main.wms_id
RIGHT JOIN portal_gm_cron AS db_cron ON db_cron.id_wms = db_main.wms_id
WHERE db_main.user_id = 1
ORDER BY time DESC
LIMIT 50

CREATE TABLE IF NOT EXISTS `portal_gm_provide` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `wms_id` int(10) unsigned NOT NULL DEFAULT '0',
  `mail_notify` varchar(100) DEFAULT NULL,
  `mail_period` varchar(100) DEFAULT NULL,
  `time` datetime NOT NULL,
  `time_cron` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `portal_gm_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(10) unsigned NOT NULL DEFAULT '0',
  `subcode` int(10) unsigned DEFAULT '0',
  `id_wms` int(10) unsigned DEFAULT '0',
  `id_layer` int(10) unsigned DEFAULT '0',
  `message` mediumtext,
  `message_real` mediumtext,
  `counter` int(10) unsigned NOT NULL DEFAULT '1',
  `time_first` datetime NOT NULL,
  `time_last` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_wms` (`id_wms`),
  KEY `id_layer` (`id_layer`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `portal_gm_cron` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_wms` int(10) unsigned DEFAULT '0',
  `error` varchar(250) DEFAULT NULL,
  `errorno` int(10) unsigned DEFAULT '0',
  `flag` int(10) unsigned DEFAULT '0',
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `GM_ID` (`id_wms`),
  KEY `DATUM` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `portal_gm_provide` (`id`, `user_id`, `wms_id`, `mail_notify`, `mail_period`, `time`, `time_cron`) VALUES
    (1, 1, 2, 'MOD_GEOMONITOR_PROVIDE_NOTIFY_STATE_WARNINGERROR', 'MOD_GEOMONITOR_PROVIDE_NOTIFY_PERIOD_3H', '2011-04-21 10:35:36', '2011-05-13 10:10:02'),
    (2, 1, 1389, 'MOD_GEOMONITOR_PROVIDE_NOTIFY_STATE_WARNINGERRORIN', 'MOD_GEOMONITOR_PROVIDE_NOTIFY_PERIOD_IMMEDIATELY', '2011-05-02 13:42:02', '2011-05-13 11:34:01'),
    (3, 1, 415, 'MOD_GEOMONITOR_PROVIDE_NOTIFY_STATE_WARNINGERRORIN', 'MOD_GEOMONITOR_PROVIDE_NOTIFY_PERIOD_IMMEDIATELY', '2011-05-02 13:42:02', '2011-05-13 11:53:01');

INSERT INTO `portal_gm_log` (`id`, `code`, `subcode`, `id_wms`, `id_layer`, `message`, `message_real`, `counter`, `time_first`, `time_last`) VALUES (578, 8192, 0, 415, 5975, 'Valid SRS not found. Maybe no matching BoundingBox. Defaulting to EPSG:4326.', NULL, 90, '2010-10-21 20:19:42', '2010-10-27 13:42:52');
INSERT INTO `portal_gm_log` (`id`, `code`, `subcode`, `id_wms`, `id_layer`, `message`, `message_real`, `counter`, `time_first`, `time_last`) VALUES (3984, 8192, 0, 415, 5975, 'No valid Box for given SRS not found. Maybe no matching BoundingBox. Defaulting to EPSG:4326.', NULL, 483, '2010-10-27 15:16:05', '2010-12-02 08:18:19');
INSERT INTO `portal_gm_log` (`id`, `code`, `subcode`, `id_wms`, `id_layer`, `message`, `message_real`, `counter`, `time_first`, `time_last`) VALUES (7800, 4352, 0, 415, 0, 'www.umweltkarten.mv-regierung.de', 'every name server provided has failed: Success', 3, '2010-11-26 00:37:05', '2010-11-29 21:34:27');
INSERT INTO `portal_gm_log` (`id`, `code`, `subcode`, `id_wms`, `id_layer`, `message`, `message_real`, `counter`, `time_first`, `time_last`) VALUES (14471, 12288, 0, 1389, 19219, '', NULL, 95, '2011-05-02 14:24:01', '2011-05-13 10:40:51');

INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2530349, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 00:00:27');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2531615, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 02:48:49');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2532882, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 05:36:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2534150, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 08:16:10');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2535418, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 11:04:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2536684, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 13:56:07');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2537951, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 16:44:20');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2539219, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 19:24:07');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2540489, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 21:56:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2541758, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 00:40:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2543027, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 03:24:01');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2544298, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 06:00:07');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2545566, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 08:48:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2546836, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 11:24:19');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2548106, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 14:00:09');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2549374, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 16:44:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2550643, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 19:24:01');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2551912, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 22:04:02');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2553181, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 00:44:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2554451, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 03:20:10');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2555719, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 06:04:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2556987, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 08:48:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2558256, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 11:28:06');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2559523, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 14:20:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2560791, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 17:08:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2562060, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 19:48:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2563328, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 22:28:02');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2564598, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-09 01:04:39');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2531247, 1389, NULL, 0, 5, '2011-05-06 01:36:55');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2532514, 1389, NULL, 0, 5, '2011-05-06 04:24:25');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2533782, 1389, NULL, 0, 5, '2011-05-06 07:04:47');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2535050, 1389, NULL, 0, 5, '2011-05-06 09:48:36');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2536316, 1389, NULL, 0, 5, '2011-05-06 12:44:14');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2537582, 1389, NULL, 0, 5, '2011-05-06 15:36:12');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2538850, 1389, NULL, 0, 5, '2011-05-06 18:16:10');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2541389, 1389, NULL, 0, 5, '2011-05-06 23:28:48');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2542660, 1389, NULL, 0, 5, '2011-05-07 02:04:43');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2543929, 1389, NULL, 0, 5, '2011-05-07 04:48:38');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2545198, 1389, NULL, 0, 5, '2011-05-07 07:32:29');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2546468, 1389, NULL, 0, 5, '2011-05-07 10:12:55');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2547737, 1389, NULL, 0, 5, '2011-05-07 12:52:45');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2549006, 1389, NULL, 0, 5, '2011-05-07 15:32:34');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2550275, 1389, NULL, 0, 5, '2011-05-07 18:08:02');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2551545, 1389, NULL, 0, 5, '2011-05-07 20:48:32');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2552813, 1389, NULL, 0, 5, '2011-05-07 23:32:46');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2554083, 1389, NULL, 0, 5, '2011-05-08 02:08:56');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2555351, 1389, NULL, 0, 5, '2011-05-08 04:52:26');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2556621, 1389, NULL, 0, 5, '2011-05-08 07:28:48');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2557888, 1389, NULL, 0, 5, '2011-05-08 10:16:33');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2559156, 1389, NULL, 0, 5, '2011-05-08 13:00:21');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2560423, 1389, NULL, 0, 5, '2011-05-08 15:52:43');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2561692, 1389, NULL, 0, 5, '2011-05-08 18:36:16');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2562960, 1389, NULL, 0, 5, '2011-05-08 21:16:01');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2564229, 1389, NULL, 0, 5, '2011-05-08 23:56:02');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2565498, 1389, NULL, 0, 5, '2011-05-09 02:32:19');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2566768, 1389, NULL, 0, 5, '2011-05-09 05:08:43');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2568037, 1389, NULL, 0, 5, '2011-05-09 07:48:32');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2569304, 1389, NULL, 0, 5, '2011-05-09 10:40:46');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2570571, 1389, NULL, 0, 5, '2011-05-09 13:28:45');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2571838, 1389, NULL, 0, 5, '2011-05-09 16:16:36');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2573106, 1389, NULL, 0, 5, '2011-05-09 18:56:10');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2574373, 1389, NULL, 0, 5, '2011-05-09 21:40:38');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2575640, 1389, NULL, 0, 5, '2011-05-10 00:24:09');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2576906, 1389, NULL, 0, 5, '2011-05-10 03:16:30');
I开发者_高级运维NSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2578175, 1389, NULL, 0, 5, '2011-05-10 05:52:55');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2579443, 1389, NULL, 0, 5, '2011-05-10 08:32:28');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2580711, 1389, NULL, 0, 5, '2011-05-10 11:20:34');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2581979, 1389, NULL, 0, 5, '2011-05-10 14:00:46');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2583239, 1389, NULL, 0, 5, '2011-05-10 16:32:32');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2584511, 1389, NULL, 0, 5, '2011-05-10 19:04:14');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2585784, 1389, NULL, 0, 5, '2011-05-10 21:29:00');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2587056, 1389, NULL, 0, 5, '2011-05-10 23:56:21');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2589600, 1389, NULL, 0, 5, '2011-05-11 04:56:32');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2590873, 1389, NULL, 0, 5, '2011-05-11 07:20:50');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2592143, 1389, NULL, 0, 5, '2011-05-11 10:00:47');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2593414, 1389, NULL, 0, 5, '2011-05-11 12:36:19');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2594682, 1389, NULL, 0, 5, '2011-05-11 15:28:39');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2595953, 1389, NULL, 0, 5, '2011-05-11 18:04:23');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2597226, 1389, NULL, 0, 5, '2011-05-11 20:28:09');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2598498, 1389, NULL, 0, 5, '2011-05-11 22:56:59');

Not sure if I understand exactly what you mean, but I think you are asking for each row from the three tables to be displayed as one using all the columns from each table (putting nulls for the columns from other tables) and sorting all the rows in date order (most recent first).

Using your code as an example (to pick date columns and filters), this should do:

SELECT o.*
FROM (
(SELECT id_wms, time AS ordered_time, id, error, errorno, flag, NULL AS code, NULL AS subcode, NULL AS id_layer, NULL AS message, NULL AS message_real, NULL AS counter, NULL AS time_first, NULL AS time_last, NULL AS user_id, NULL AS mail_notify, NULL AS mail_period, NULL AS time_cron
FROM
    portal_gm_cron)
UNION ALL
(SELECT id_wms, time_first AS ordered_time, id, NULL AS error, NULL AS errorno, NULL AS flag, code, subcode, id_layer, message, message_real, counter, time_first, time_last, NULL AS user_id, NULL AS mail_notify, NULL AS mail_period, NULL AS time_cron
FROM
    portal_gm_log)
UNION ALL
(SELECT wms_id as id_wms, time AS ordered_time, id, NULL AS error, NULL AS errorno, NULL AS flag, NULL AS code, NULL AS subcode, NULL AS id_layer, NULL AS message, NULL AS message_real, NULL AS counter, NULL AS time_first, NULL AS time_last, user_id, mail_notify, mail_period, time_cron
FROM
    portal_gm_provide
WHERE
    user_id = 1) 
) AS o
ORDER BY ordered_time DESC

You can alter column order and date column selection to suit.