SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
-
Feb 15, 2006, 19:05 #1
- Join Date
- Dec 2004
- Location
- New Castle, PA
- Posts
- 141
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Odd issue with DISTINCT in 5.0.15-nt
Running on Windows Server 2003
I've got a table with ticket issues in it, each having its own status (of about 6 different status types). The most simple query
Code:SELECT DISTINCT ticket_status FROM new_tickets WHERE ticket_status <> "closed" ORDER BY ticket_status ASC
table structure:
Code:DROP TABLE IF EXISTS `ims`.`new_tickets`; CREATE TABLE `ims`.`new_tickets` ( `billable` varchar(5) default '', `ticket_unique_id` varchar(10) NOT NULL default '0', `ticket_status` varchar(25) NOT NULL default 'new', `new_ticket_date` date NOT NULL default '0000-00-00', `new_ticket_time` time NOT NULL default '00:00:00', `new_ticket_real_time` time NOT NULL default '00:00:00', `new_ticket_real_date` date NOT NULL default '0000-00-00', `ticket_tech` varchar(50) NOT NULL default '', `assigned_to` varchar(25) NOT NULL default '', `billed` varchar(5) NOT NULL default '', `client_unique_id` varchar(10) NOT NULL default '', PRIMARY KEY (`ticket_unique_id`), KEY `ticket_tech` (`ticket_tech`), KEY `ticket_status` (`ticket_status`), KEY `client_unique_id` (`client_unique_id`), KEY `ticket_unique_id` (`ticket_unique_id`), KEY `billable` (`billable`), KEY `assigned_to` (`assigned_to`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-
Feb 15, 2006, 19:29 #2
- Join Date
- Dec 2004
- Location
- New Castle, PA
- Posts
- 141
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
UPDATE:
this makes it work, but seems awful silly to have to add it....
Code:SELECT COUNT(DISTINCT ticket_status) as CStat, ticket_status FROM new_tickets WHERE ticket_status <> 'closed' GROUP BY ticket_status
-
Feb 15, 2006, 20:10 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
could the difference perhaps be "closed" versus 'closed'?
i don't have 5 to test on, so i'm guessing
according to standard sql, "closed" is a column name, while 'closed' is a string
i heard that version 5 is a little more rigorous about standards than previous versions
-
Feb 15, 2006, 21:53 #4
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
also, try running a repair on your table. your first query only looks at your indexes, whereas your second query probably results in a table scan and skips the indexes. if your index is damaged, the repair will fix it.
Bookmarks