SQL MAX() function? - SQL Server Forums

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)

SQL MAX() function?

Author

Topic

biff
Starting Member

6 Posts

Posted - 2004-12-05 : 14:43:40

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-05 : 15:11:21

Change the select to T5.action_timestamp
and the join to
INNER JOIN (select entity_dbid, action_timestamp = max(action_timestamp) from CQ_Owner.history where 16777240 = entitydef_id) T5 ON T1.dbid = T5.entity_dbid

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

biff
Starting Member

6 Posts

Posted - 2004-12-05 : 15:15:42

Thanks for the quick reply!!

is this what you mean?

select distinct
T5.action_timestamp

from ( ( ( ( ( ( CQ_Owner.Defect T1

INNER JOIN (select entity_dbid, action_timestamp = max(action_timestamp) from CQ_Owner.history where 16777240 = entitydef_id T5 ON T1.dbid = T5.entity_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

****** this gives a syntax error

thanks..

biff
Starting Member

6 Posts

Posted - 2004-12-05 : 15:16:06

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-05 : 15:37:31

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,
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 (select entity_dbid, action_timestamp = max(action_timestamp) from CQ_Owner.history where 16777240 = entitydef_id) T5 ON T1.dbid = T5.entity_dbid
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

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

biff
Starting Member

6 Posts

Posted - 2004-12-05 : 18:13:15

Thanks nr..!

i get this error now:

Incorrect syntax near the keyword 'where'

i have changed a few things but still get a syntax error.. any ideas?

thanks

biff
Starting Member

6 Posts

Posted - 2004-12-05 : 18:25:52

I found a solution to this.. thanks for your responses as i wouldnt have gotten this without your help... heres the 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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-05 : 19:04:43

I missed out the group by in the derived table

INNER JOIN (select entity_dbid, action_timestamp = max(action_timestamp) from CQ_Owner.history where 16777240 = entitydef_id group by entity_dbid) T5 ON T1.dbid = T5.entity_dbid

Bit surprised it gives that error though - must have missed a bracket.
Another option is to use a subquery in the select

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,
action_timestamp = (select max(action_timestamp) from CQ_Owner.history T5 where 16777240 = T5.entitydef_id and T1.dbid = T5.entity_dbid)
from

You also don't need the distinct with the group by clause.
The problem with the froup by is that it can create a very large work table if you have a lot of fields and take a long time.

You might also get rid of a lot of hte brackets for clarity
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

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources