CODEWITHSUNDEEP
mysqlsql
Prince Singh
Prince Singh

Reputation: 5183

order by date and time stored in two different column mysql

basic structure of table

event_id | event_name | start_time | end_time | date

I am trying to fetch events in the sequence they ended

query i tried :-

SELECT * FROM `tablename` ORDER BY date,end_time DESC

but I am not getting expected result.

PS : end_time has type time and date has type date

Edit : table dump to test

CREATE TABLE IF NOT EXISTS `wp_deals` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `p_id` bigint(20) NOT NULL,
  `end_time` time NOT NULL,
  `start_time` time NOT NULL,
  `lastactive` date NOT NULL,
  `alldates` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `live` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=17 ;

--
-- Dumping data for table `wp_deals`
--

INSERT INTO `wp_deals` (`id`, `title`, `p_id`, `end_time`, `start_time`, `lastactive`, `alldates`, `status`, `live`) VALUES
(12, 'checking', 70, '13:00:00', '12:30:00', '2014-08-11', '2014-08-09,2014-08-11', 'completed', 1),
(13, 'dealajax', 11, '12:00:00', '11:30:00', '2014-08-11', '2014-08-09,2014-08-11', 'completed', 1),
(16, 'testprodone', 11, '11:40:00', '10:48:00', '2014-08-10', '2014-08-10', 'completed', 1),
(11, 'report test', 70, '11:45:00', '11:35:00', '2014-08-09', '2014-08-09', 'completed', 1),
(9, 'next deal', 70, '15:50:00', '09:20:00', '2014-08-08', '2014-08-07,2014-08-08', 'completed', 1),
(10, 'Deal 2', 11, '12:30:00', '12:00:00', '2014-08-11', '2014-08-08,2014-08-11', 'completed', 1);

Upvotes: 1

Views: 1523

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

My guess is that you want the most recent dates first. If so, you need desc twice:

SELECT *
FROM `tablename`
ORDER BY `date` DESC, `end_time` DESC;

Your version gets the latest times from the earliest dates. That doesn't seem quite as useful.

Upvotes: 3

Related Questions