MySQL Forums
Forum List  »  Perl

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 ?

Options: ReplyQuote


Subject
Written By
Posted
possible bug using select distinct
March 19, 2008 04:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.