Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005
    Posts
    16

    Unanswered: help with GROUP BY

    I have a table of customer problems, and another table of cummunications relating to the problem.

    I'm trying to pull out for each problem: details of the problem, the most recent comversation, and the timestamp of the oldest conversation.

    I've got the latest conversation like so:
    Code:
    select * from
    tProblems p 
    INNER JOIN 
    (
    SELECT * FROM tProblemComments order BY uts desc
    ) as pc ON p.poNumber = pc.poNumber
    WHERE open = 1
    group by p.poNumber
    order by p.poNumber asc
    Though this probably isn't the best way forward to proper solution, and I can't think how to do it.

    Any ideas?

    Heres the ddl:
    Code:
    CREATE TABLE `tproblems` (
      `poNumber` bigint(20) NOT NULL default '0',
      `contact` varchar(100) NOT NULL default '',
      `phone` varchar(100) NOT NULL default '',
      `open` tinyint(4) default NULL,
      PRIMARY KEY  (`poNumber`),
      KEY `closed` (`open`)
    ) 
    
    CREATE TABLE `tproblemcomments` (
      `uts` int(11) NOT NULL default '0',
      `poNumber` int(11) NOT NULL default '0',
      `relevance` varchar(100) NOT NULL default '',
      `comment` text NOT NULL,
      `staff` varchar(20) NOT NULL default '',
      KEY `uts` (`uts`),
      KEY `poNumber` (`poNumber`)
    )

  2. #2
    Join Date
    Feb 2005
    Location
    Germany
    Posts
    22
    the order by in the subquery is not necessare i guess:

    select * from
    tProblems p
    INNER JOIN
    tProblemComments as pc
    ON p.poNumber = pc.poNumber
    WHERE open = 1
    group by p.poNumber
    order by p.poNumber asc

Posting Permissions

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