Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  MAX() problem.. i think

Author  Topic  biff Starting Member 6 Posts Posted - 2004-12-13 : 19:40:23 Hi All; I have an SQL Qeury that Groups a certain way and then takes the MAX() of a date and displays unique records.. this appears to work fine in one aspect. When i added another field from another table though, it no longer takes the oldest date and displays unique records but displays all the records. Heres the Query: ** the only field i added to this was the T43.login_name field and it broke ..! select distinct T1.dbid, T1.id, T2.login_name, T3.callid, T3.companyid, T4.login_name, T43.login_name, T1.headline, T1.severity, T1.priority, T1.submit_date,max(T5.action_timestamp) from ( ( ( ( ( ( ( CQ_Owner.Defect T1 INNER JOIN CQ_Owner.users T2 ON T1.submitter = T2.dbid ) INNER JOIN CQ_Owner.users T4 ON T1.owner = T4.dbid ) INNER JOIN CQ_Owner.users T43 ON T1.mtester = T43.dbid ) INNER JOIN CQ_Owner.history T5 ON T1.dbid = T5.entity_dbid and 16777240 = T5.entitydef_id ) INNER JOIN CQ_Owner.statedef T6 ON T1.state = T6.id ) LEFT OUTER JOIN CQ_Owner.parent_child_links T3mm ON T1.dbid = T3mm.parent_dbid and 16779300 = T3mm.parent_fielddef_id ) LEFT OUTER JOIN CQ_Owner.gecontactlist T3 ON T3mm.child_dbid = T3.dbid ) where T1.dbid <> 0 and ((T1.mscrtype in ('Escalation') and T1.mproduct like '%EmailXaminer%' and (T6.name not in ('Closed','Duplicate','Resolved') or T1.state = 0))) group by T1.dbid,T1.id,T2.login_name,T3.callid,T3.companyid,T4.login_name,T43.login_name,T1.headline, T1.severity,T1.priority,T1.submit_date,T5.action_timestamp order by T1.dbid ASC, max(T5.action_timestamp) DESC Any ideas? thanks in advance.. j