SQL Max() function?

[LGTOserv]

Hi All;

I am having a problem with a query that i hope someone can help with. I have created a query to retrieve records from a database. This works fine but i want to only show the latest date of one of the fields as this query returns many duplicate id entries on different dates. I assume i need the MAX(date) function but get an error when i use it. the error i get is:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

I am only trying to return the "action_timestamp" latest date entry for id.

here is the query:

select distinct
T1.dbid,
T1.id,
T2.login_name,
T3.callid,
T4.login_name,
T3.companyid,
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.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)))

order by T1.id ASC,T5.action_timestamp DESC


any help would be appreciated..
thanks in advance

 

[dbomrrsm]

select distinct
T1.dbid,
T1.id,
T2.login_name,
T3.callid,
T4.login_name,
T3.companyid,
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.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.id
order by T1.id ASC,max(T5.action_timestamp) DESC

[blue]DBomrrsm[/blue]

 

[LGTOserv]

Wow, thanks for the swift reply; i tried this and get another error:

Column 'T1.dbid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

any ideas?

thanks again..!!!

 

[yksvaan]

The second last row, I think dbomrrsm ment it to be

GROUP BY T1.dbid,T1.id,T2.login_name,T3.callid,T4.login_name,T3.companyid,T1.headline,T1.severity,T1.priority,T1.submit_date

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[LGTOserv]

Ahh yes.. everything worked with that.. thanks very much everyone for responding.. your all very helpfull

final solution:

select distinct
T1.dbid,
T1.id,
T2.login_name,
T3.callid,
T4.login_name,
T3.companyid,
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.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,T4.login_name,T3.companyid,T1.headline,T1.severity,T1.priority,T1.submit_date
order by T1.id ASC,max(T5.action_timestamp) DESC