Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2010
    Posts
    9

    Unhappy Unanswered: MySQL SELECT distinct & group by

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by betas View Post
    There is something wrong with the query I'm using since the ticket.number column should return distinct values...
    you forgot the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    9
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by betas View Post
    Isn't that the clause?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2010
    Posts
    9
    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!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by betas View Post
    I'm getting these rows:
    then something else is wrong, because if you have GROUP BY ticket.number then you will get only one row per ticket number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2010
    Posts
    9
    Any ideas on what might be wrong? I can't seem to figure out why I'm getting more than one row per ticket.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2010
    Posts
    9
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2010
    Posts
    9
    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!

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think this may be more of what you are looking for:

    Code:
    SELECT ticket.room, ticket.status, ticket.number, agenda.name
       , ticket.datum_status
        FROM ticket, agenda
    WHERE ticket.status IN(3,4)
       and ticket.datum_status = (select max(ticket1.datum_status)
                                                from ticket ticket1
                                            where ticket.number = ticket1.number
                                                and ticket1.status in (3,4))
       AND agenda.idagenda = ticket.idagenda
    ORDER BY ticket.datum_status DESC LIMIT 6;
    Dave

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --
    Code:
    SELECT ticket.room, ticket.status, ticket.number
          , MAX(ticket.datum_status) 
      FROM ticket
     WHERE ticket.status IN(3,4) 
    GROUP BY ticket.number
    ORDER BY ticket.datum_status DESC LIMIT 6;
    and here's the data it produced --
    Code:
    room status number   MAX(ticket.datum_status)
      2     4   FELO-7   2010-02-11 16:45:28
      2     3   FELO-6   2010-02-11 16:45:17
      2     4   TR10/35  2010-02-11 16:41:50
      2     4   TR10/21  2010-02-11 16:41:27
      2     4   TR10/2   2010-02-11 16:41:12
      2     4   TR10/38  2010-02-12 09:48:51
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •