help with GROUP BY

hessodreamy · #1 (**permalink**) 12-02-05, 08:22

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

msueper · #2 (**permalink**) 12-09-05, 09:53

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

Internet Services

Web Development

Digital Marketing

Consumer Tech