Another Join Question

robinjohn

Hi Everyone,

I've tried to write a query that will select the latest record for each T_Number. It sounds easy, but my attempt is off somehow - I'm getting the most recent record for each T_Number, but the contents of some of the selected fields don't come from the record I'm trying to select. There'll usually be more than one T_Number for any given D_Name

for practical examples:

select T_Number, T_TStamp, H_Remark from TicketHist where D_Name = 'whatever.com'

T_Number	T_TStamp	H_Remark
541	2003-08-21 10:04:09	
541	2003-08-21 12:28:30	remark1
541	2003-08-21 12:28:42	remark2.
541	2003-08-21 14:21:36	remark3.

Then a select for the Max T_TStamp

select Max(T_TStamp) from TicketHist where D_Name = 'whatever.com'
Max(T_TStamp)
2003-08-21 14:21:36

but finally

select T_Number, Max(T_TStamp), H_Remark from TicketHist where D_Name = 'whatever.com' 
group by T_Number
T_Number	Max(T_TStamp)	           H_Remark
541	          2003-08-21 14:21:36

Correct T_Number + T_TStamp - but using the wrong H_Remark field. Why is this?

Can anyone point out where I'm going wrong? Or have any suggestions?
Thanks!
Rob.

Lars_Berg

http://www.phpbuilder.com/board/showthread.php?t=10352025

Roger_Ramjet

A more fullsome answer would be that MySQL has its own particular behaviour with GROUP BY. If you do not include a column in the group by clause and the values in that column differ from row to row, mysql will return a value from a 'random' row. (It's not random but we don't need to go into that here).

Lars link shows you how to get you the results you want, and will work on any rdbms.

robinjohn

Hi Guys - cheers for the info - it's a new one to me.

After trying it out a bit I came up with this :


$query = "select T_Number, DATE_FORMAT(T_TStamp,'%d-%m-%Y') As Date, 
TicketAdminStatus.Description As AdminStatus, TicketTechStatus.Description As 
TechStatus, D_Name, D_Holder, T_Type, Bill_NH, Admin_NH1, Tech_NH, Chng_NH, 
Opt_Cert, H_Remark from TicketHist TH1 INNER JOIN TicketAdminStatus ON  

TH1.Admin_Status = TicketAdminStatus.Status INNER JOIN TicketTechStatus ON 
Tech_Status = TicketTechStatus.Status where T_TStamp IN (Select Max(T_TStamp) 
from TicketHist TH2 where D_Name = '" . $domain . "' group by T_Number) and 
D_Name = '" . $domain . "' group by T_Number;";

It seems to solve the problem - now on to the next!
Rob.