possible bug using select distinct
Posted by:
Sean Hayes
Date: March 19, 2008 04:58AM
Hi, I have a table called courses
CREATE TABLE `courses_available` (
`id` int(8) NOT NULL auto_increment,
`course_type` varchar(8) NOT NULL,
`start_date` date NOT NULL,
`date_desc` varchar(255) NOT NULL,
`venue` varchar(255) NOT NULL,
`price_ex_vat` int(6) NOT NULL,
`vat_rate` decimal(6,2) NOT NULL,
`status` int(1) NOT NULL,
`submission_date` datetime NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=35 DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;
--
-- Dumping data for table `courses_available`
--
INSERT INTO `courses_available` VALUES (34, 'Storage', '2008-09-29', '29 - 30 September 2008', 'TBC ', 2095, 19.00, 1, '2008-03-18 16:38:37');
INSERT INTO `courses_available` VALUES (30, 'IGP', '2008-10-21', '21 October 2008 ', 'London, UK ', 560, 17.50, 1, '2007-10-17 13:44:23');
INSERT INTO `courses_available` VALUES (23, 'TGC', '2008-03-30', '30 March - 4 April 2008 ', 'Hilton, Prague ', 3590, 19.00, 1, '2007-11-20 10:54:03');
INSERT INTO `courses_available` VALUES (24, 'MGM', '2008-05-12', '12 - 15 May 2008 ', 'Crowne Plaza City Centre, Berlin, Germany ', 2570, 19.00, 1, '2008-03-06 14:15:46');
INSERT INTO `courses_available` VALUES (25, 'LNGII', '2008-05-20', '20 - 23 May 2008 ', 'Le Meridien N''Fis, Marrakech, Morocco ', 2470, 17.50, 1, '2008-03-11 14:36:25');
INSERT INTO `courses_available` VALUES (26, 'IGP', '2008-06-17', '17 June 2008 ', 'London, UK ', 560, 17.50, 1, '2007-08-23 16:45:31');
INSERT INTO `courses_available` VALUES (27, 'LNG', '2008-06-22', '22 - 27 June 2008 ', 'NH Calderón Hotel, Barcelona, Spain ', 3700, 19.00, 1, '2008-03-06 12:32:17');
INSERT INTO `courses_available` VALUES (29, 'LNG', '2008-10-12', '12 - 17 October 2008 ', 'TBC ', 3700, 17.50, 1, '2007-12-04 13:05:46');
INSERT INTO `courses_available` VALUES (28, 'Storage', '2008-04-29', '29 - 30 April 2008 ', 'Visconti Palace, Rome, Italy', 2095, 20.00, 1, '2008-03-06 14:13:45');
INSERT INTO `courses_available` VALUES (31, 'TGC', '2008-09-14', '14 - 19 September 2008 ', 'Dolce Chantilly, Paris ', 3590, 19.00, 1, '2007-10-17 13:46:01');
INSERT INTO `courses_available` VALUES (32, 'TGC', '2008-11-30', '30 November - 5 December 2008 ', 'Munich, Germany ', 3590, 19.00, 1, '2007-10-17 13:47:32');
INSERT INTO `courses_available` VALUES (33, 'MGM', '2008-11-10', '10 - 13 November 2008', 'TBC ', 2570, 17.50, 1, '2007-12-04 13:06:30');
I run the following statement against it
SELECT DISTINCT(course_type)FROM courses_available WHERE (TO_DAYS(start_date) > TO_DAYS(now() ) ) ORDER BY start_date ASC
& i get the following order
TGC
MGM
LNGII
LNG
Storage
IGP
When in fact the order should be
TGC
Storage
MGM
LNGII
IGP
LNG
Any ideas ?