Hello,

I'm using this query:

SELECT ticket.room, ticket.status, ticket.number, agenda.name, MAX(ticket.datum_status) FROM ticket, agenda WHERE ticket.status IN(3,4) AND agenda.idagenda = ticket.idagenda GROUP BY ticket.number, ticket.datum_status ORDER BY ticket.datum_status DESC LIMIT 6;

And I'm getting these results:

'2', 4, 'TR10/38', 'TRA-01.', '2010-02-12 09:48:51'
'2', 3, 'TR10/38', 'TRA-01.', '2010-02-12 09:48:27'
'2', 4, 'FELO-7', 'FELO', '2010-02-11 16:45:28'
'2', 4, 'FELO-6', 'FELO', '2010-02-11 16:45:17'
'2', 4, 'FELO-6', 'FELO', '2010-02-11 16:45:06'
'2', 4, 'FELO-7', 'FELO', '2010-02-11 16:44:54'

There is something wrong with the query I'm using since the ticket.number column should return distinct values, but as you can see TR10/38 & FELO-6 are duplicated.

Any ideas?

Thanks!

you forgot the GROUP BY clause

SELECT ticket.room, ticket.status, ticket.number, agenda.name, MAX(ticket.datum_status) FROM ticket, agenda WHERE ticket.status IN(3,4) AND agenda.idagenda = ticket.idagenda GROUP BY ticket.number, ticket.datum_status ORDER BY ticket.datum_status DESC LIMIT 6;

Isn't that the clause?

Thanks

yes, you're right, my apologies, no coffee yet this morning...

so, what was your question again?

when you say GROUP BY ticket.number, ticket.datum_status, you will get one result row for every unique combination of values in those two columns

so every ticket.datum_status within each ticket.number will get a separate result

since this does not appear to be what you want, let me suggest that you remove ticket.datum_status from the GROUP BY clause

Hello,

I'm getting these rows:

'2', 4, 'TR10/38', 'TRA-01.', '2010-02-12 09:48:51'
'2', 3, 'TR10/38', 'TRA-01.', '2010-02-12 09:48:27'
'2', 4, 'FELO-7', 'FELO', '2010-02-11 16:45:28'
'2', 4, 'FELO-6', 'FELO', '2010-02-11 16:45:17'
'2', 4, 'FELO-6', 'FELO', '2010-02-11 16:45:06'
'2', 4, 'FELO-7', 'FELO', '2010-02-11 16:44:54'

But what I need are the following rows to be returned:

'2', 4, 'TR10/38', 'TRA-01.', '2010-02-12 09:48:51'
'2', 4, 'FELO-7', 'FELO', '2010-02-11 16:45:28'
'2', 4, 'FELO-6', 'FELO', '2010-02-11 16:45:17'
'2', 4, 'FELO-7', 'FELO', '2010-02-11 16:44:54'
Next 2 rows

The ticket.number are returning two non distinct values.

Thanks!

then something else is wrong, because if you have GROUP BY ticket.number then you will get only one row per ticket number

Any ideas on what might be wrong? I can't seem to figure out why I'm getting more than one row per ticket.

ticket number might have some unprintable character in it, like a space?

say, why are you joining to the other table? you don't appear to need it...

Ticket number has no spaces. I tried removing the agenda table, but the result was the same. I really don't know what else to try.

please dump the table, i.e. generate the CREATE TABLE statement along with representative rows of data in INSERT statements

i'd like to test this myself

Here you go:

DROP TABLE IF EXISTS `ticket`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ticket` (
`id` int(32) NOT NULL AUTO_INCREMENT,
`idbooked` int(32) DEFAULT NULL,
`number` varchar(12) DEFAULT NULL,
`status` tinyint(4) DEFAULT NULL,
`datum` datetime DEFAULT NULL,
`datum_status` datetime DEFAULT NULL,
`idagenda` varchar(16) DEFAULT NULL,
`room` varchar(32) DEFAULT NULL,
`idagenda_parent` varchar(16) DEFAULT NULL,
`iduser` int(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4264 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `ticket` WRITE;
/*!40000 ALTER TABLE `ticket` DISABLE KEYS */;
INSERT INTO `ticket` VALUES (4270,4303,'TR10/36',4,NULL,'2010-02-11 16:39:14','TR10','2',NULL,44),(4271,4158,'TR10/1',4,NULL,'2010-02-11 16:39:44','TR10','2',NULL,44),(4272,4307,'TR10/38',4,NULL,'2010-02-11 16:40:09','TR10','2',NULL,44),(4273,4307,'TR10/38',4,NULL,'2010-02-11 16:40:17','TR10','2',NULL,44),(4274,4307,'TR10/38',4,NULL,'2010-02-11 16:40:33','TR10','2',NULL,44),(4275,4159,'TR10/2',4,NULL,'2010-02-11 16:41:04','TR10','2',NULL,44),(4276,4159,'TR10/2',4,NULL,'2010-02-11 16:41:12','TR10','2',NULL,44),(4277,4219,'TR10/21',4,NULL,'2010-02-11 16:41:27','TR10','2',NULL,44),(4278,4303,'TR10/36',4,NULL,'2010-02-11 16:41:39','TR10','2',NULL,44),(4279,4291,'TR10/35',4,NULL,'2010-02-11 16:41:50','TR10','2',NULL,44),(4280,4307,'TR10/38',4,NULL,'2010-02-11 16:42:01','TR10','2',NULL,44),(4281,4331,'FELO-6',1,'2010-02-11 16:43:28','2010-02-11 16:43:28','FELO',NULL,NULL,NULL),(4282,4331,'FELO-6',3,NULL,'2010-02-11 16:43:57','FELO','2',NULL,40),(4283,4331,'FELO-6',4,NULL,'2010-02-11 16:44:07','FELO','2',NULL,40),(4284,4334,'FELO-7',4,NULL,'2010-02-11 16:44:39','FELO','2',NULL,40),(4285,4334,'FELO-7',4,NULL,'2010-02-11 16:44:54','FELO','2',NULL,40),(4286,4331,'FELO-6',4,NULL,'2010-02-11 16:45:06','FELO','2',NULL,40),(4287,4331,'FELO-6',4,NULL,'2010-02-11 16:45:17','FELO','2',NULL,40),(4288,4334,'FELO-7',4,NULL,'2010-02-11 16:45:28','FELO','2',NULL,40),(4289,4307,'TR10/38',5,NULL,'2010-02-12 09:48:16','TRA-01','2','',52),(4290,4307,'TR10/38',3,NULL,'2010-02-12 09:48:27','TRA-01','2',NULL,52),(4291,4307,'TR10/38',4,NULL,'2010-02-12 09:48:51','TRA-01','2',NULL,52);
/*!40000 ALTER TABLE `ticket` ENABLE KEYS */;
UNLOCK TABLES;

Thanks!

I think this may be more of what you are looking for:

Dave

dave, you leapfrogged right over what i was gonna teach him

betas, since you did not provide the agenda table, i removed it from the query

here's your query that i tried --and here's the data it produced --see? one row per ticket.number

notice, by the way, that the ORDER BY did not work correctly, i can explain this if you wish