rendered paste body-- MySQL dump 10.11
--
-- Host: localhost Database: vatis
-- ------------------------------------------------------
-- Server version 5.0.51a-3ubuntu5.1
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `tickets`
--
DROP TABLE IF EXISTS `tickets`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `tickets` (
`ticket_id` mediumint(8) unsigned NOT NULL auto_increment,
`status` enum('open','closed','waiting','progress','delete') collate utf8_unicode_ci NOT NULL,
`type` enum('query','answer') collate utf8_unicode_ci NOT NULL,
`ticket_ref` mediumint(8) NOT NULL default '0',
`subject` text collate utf8_unicode_ci NOT NULL,
`client_id` smallint(6) default NULL,
`q_inbound` enum('email','phone','fax','teamspace','other') collate utf8_unicode_ci default NULL,
`q_class` tinyint(3) default NULL,
`q_type` varchar(255) collate utf8_unicode_ci default NULL,
`descr` longtext collate utf8_unicode_ci NOT NULL,
`assigned_login` varchar(255) collate utf8_unicode_ci NOT NULL,
`remarks` mediumtext collate utf8_unicode_ci,
`creation_date` datetime NOT NULL,
`creation_login` varchar(255) collate utf8_unicode_ci NOT NULL,
`last_edit` datetime default NULL,
`last_login` varchar(255) collate utf8_unicode_ci default NULL,
`locked_by` varchar(255) collate utf8_unicode_ci default NULL,
PRIMARY KEY (`ticket_id`,`ticket_ref`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `tickets`
--
LOCK TABLES `tickets` WRITE;
/*!40000 ALTER TABLE `tickets` DISABLE KEYS */;
INSERT INTO `tickets` VALUES (15,'delete','query',0,'i_subject',27,'other',1,'Complaint','i_query','joe.user','i_remarks','2008-07-24 01:12:14','',NULL,NULL,'jules'),(2,'closed','query',0,'Subject2',22,'email',1,'Complaint','i_query','joe.user',NULL,'2008-07-18 18:06:54','',NULL,NULL,NULL),(2,'waiting','answer',1,'Answer',22,NULL,NULL,NULL,'Description','joe.user',NULL,'2008-07-19 19:36:49','',NULL,NULL,NULL),(4,'delete','query',0,'i_subject',20,'other',1,'Complaint','i_query','joe.user','i_remarks','0000-00-00 00:00:00','',NULL,NULL,NULL),(5,'delete','query',0,'i_subject',20,'other',1,'Complaint','i_query','joe.user','i_remarks','0000-00-00 00:00:00','',NULL,NULL,NULL),(13,'delete','query',0,'i_subject',20,'other',1,'Complaint','i_query','joe.user','i_remarks','2008-07-24 01:06:09','',NULL,NULL,NULL),(14,'delete','query',0,'i_subject',20,'other',1,'Complaint','i_query','joe.user','i_remarks','2008-07-24 01:07:48','',NULL,NULL,NULL),(9,'delete','query',0,'i_subject',20,'other',1,'Complaint','i_query','joe.user','i_remarks','0000-00-00 00:00:00','',NULL,NULL,NULL),(10,'delete','query',0,'i_subject',20,'other',1,'Complaint','i_query','joe.user','i_remarks','0000-00-00 00:00:00','',NULL,NULL,NULL),(11,'delete','query',0,'i_subject',20,'other',1,'Complaint','i_query','joe.user','i_remarks','0000-00-00 00:00:00','',NULL,NULL,NULL),(12,'delete','query',0,'i_subject',34,'other',3,'Complaint','i_query','thefoo.user','lvljglgclc','2008-07-22 02:24:37','',NULL,NULL,NULL),(16,'open','query',0,'Blubdindoibjwvkjbwr',27,'other',4,'Information','Bla','thefoo.user','','2008-07-24 01:14:00','',NULL,NULL,NULL),(17,'closed','query',0,'i_subject',29,'fax',3,'Information','i_query','jane.doe','i_remarks','2008-07-24 05:00:38','',NULL,NULL,NULL),(18,'open','query',0,'',30,'email',1,'Complaint','','joe.user','','2008-07-31 21:26:40','',NULL,NULL,NULL),(19,'open','query',0,'',31,'email',1,'Complaint','','joe.user','','2008-07-31 21:26:51','',NULL,NULL,NULL),(20,'waiting','query',0,'blatest',31,'other',4,'Information','vejwebv','jane.doe','','2008-08-01 03:47:32','',NULL,NULL,NULL),(21,'open','query',0,'subk',35,'other',4,'Information','dqecweec','jane.doe','','2008-08-01 03:56:43','',NULL,NULL,NULL),(22,'open','query',0,'s',31,'email',1,'Complaint','s','joe.user','','2008-08-01 04:25:06','',NULL,NULL,'jules');
/*!40000 ALTER TABLE `tickets` ENABLE KEYS */;
UNLOCK TABLES;
/**
This is your query, transcribed to my field names:
SELECT t0.*
FROM (SELECT ticket_id
, COUNT(*) AS cnt
, MIN(ticket_ref) AS ticket_ref
FROM tickets
GROUP BY ticket_id
) AS v1
JOIN tickets AS t2
ON v1.ticket_id = t2.ticket_id
AND v1.ticket_ref = t2.ticket_ref
JOIN tickets AS t0
ON v1.ticket_id = t0.ticket_id
ORDER BY t2.status
, t0.ticket_ref
*/