help with GROUP BY 1

[hessodreamy]

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:

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:

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`)
)

 

[rac2]

The timestamp of the oldest comment for the problem. Assuming uts is the timestamp.

SELECT poNumber, MIN(uts)
FROM tproblemcomments
GROUP BY poNumber

The most recent conversation for the problem.

SELECT a.comment
FROM tproblemcomments a
WHERE a.uts = ( 
              SELECT MAX(uts)
              FROM tproblemcomments
              WHERE poNumber = a.poNumber
              )

These can be used as subqueries in the SELECT list of an outer query that specifies problem details.

SELECT b.*,
       (
         SELECT MIN(uts)
         FROM tproblemcomments
         WHERE poNumber = b.poNumber
       ) AS "FirstConversationTimestamp",
       (
         SELECT a.comment
         FROM tproblemcomments a
         WHERE a.uts = ( 
              SELECT MAX(uts)
              FROM tproblemcomments
              WHERE poNumber = b.poNumber
              )
        ) AS "LatestConversation"
FROM tproblems b

The subqueries can also be JOINed as if they were tables. JOIN on poNumber and write them as GROUP BY queries.

SELECT b.*,
       c.FirstConverstionTimestamp,
       d.comment AS "LatestConversation"
FROM tproblems b
JOIN (
      SELECT poNumber, MIN(uts) AS "FirstConversationTimestamp"
      FROM tproblemcomments
      GROUP BY poNumber
     ) c ON c.poNumber = b.poNumber
JOIN (
       SELECT a.poNumber, a.comment
       FROM tproblemcomments a
       WHERE a.uts = ( 
                      SELECT MAX(uts)
                      FROM tproblemcomments
                      WHERE poNumber = a.poNumber
                     )
     ) d ON d.poNumber = b.poNumber

This latter approach was more efficient on Microsoft SQL Server; dont know about MySQL. Also dont know that the syntax is available in MySQL.

HTH.

 

[r937]

rac2, splendid answer, worth a star

the syntax (at least in this example) is the same in mysql

r937.com | rudy.ca

 

[hessodreamy]

Thanks for that. I agree it was a great answer.
Had some initial trouble with the former approach in that the subquery returned more than 1 row. Turned out I had some duplicate rows. A group by sorted that out.

When I ran each of them the first was much, much faster, taking fraction of a second, wheras the second approach took 10 seconds. Don't know if that's mysql or just my indexes.

Thanks a lot for the help!