Return Max Value from SubQuery

[Chris H]

Hi,
I'm trying to outer join to a maximum date value using a subquery in
order to return company information and the last activity date
associated. The basic working "sub" query is:

SELECT actcomp.company _id, MAX(act.due_dat e)
FROM oncd_activity_c ompany AS actcomp, oncd_activity AS act
WHERE actcomp.activit y_id = act.activity_id
GROUP BY company_id

The overall (abbreviated) query I'm trying to insert this select into
is:

SELECT oncd_company.co mpany_id,
oncd_company.co mpany_name,
act.due_date
FROM oncd_company
LEFT OUTER JOIN oncd_activity_c ompany ON (oncd_company.c ompany_id =
oncd_activity_c ompany.company_ id)
LEFT OUTER JOIN (SELECT actcomp.company _id, MAX(act.due_dat e)

FROM oncd_activity_c ompany AS actcomp, oncd_activity AS act

WHERE actcomp.activit y_id = act.activity_id

GROUP BY company_id) ON
(oncd_activity_ company.company _id = actcomp.company _id)
I'm receiving an "invalid syntax near keyword ON" error (highlight
appears on the period in "oncd_activity_ company.company _id").
Any help would be appreciated!
Thanks,
Chris.

[Erland Sommarskog]

Chris H (ch********@bro adreachpartners inc.com) writes:

LEFT OUTER JOIN (SELECT actcomp.company _id, MAX(act.due_dat e)

FROM oncd_activity_c ompany AS actcomp, oncd_activity AS act

WHERE actcomp.activit y_id = act.activity_id

GROUP BY company_id) ON
(oncd_activity_ company.company _id = actcomp.company _id)
I'm receiving an "invalid syntax near keyword ON" error (highlight
appears on the period in "oncd_activity_ company.company _id").

Aliases are mandatory for derived tables, thus you need something like:

GROUP BY company_id) AS act ON

Furthermore you cannot refer to tables in the derived table outside it,
you can only refer to your table as a whole. You must also give all columns
in the derived table a name. Here is a rewrite of your query that
demonstrates all this:

SELECT c.company_id, c.company_name, act.due_date
FROM oncd_company c
LEFT JOIN oncd_activity_c ompany ac ON c.company_id = ac.company_id
LEFT JOIN (SELECT ac.company_id, due_date = MAX(act.due_dat e)
FROM oncd_activity_c ompany ac
JOIN oncd_activity act ON ac.activity_id = act.activity_id
GROUP BY ac.company_id) act ON ac.company_id = act.company_id

Yes, the same aliases are used both inside and outside the derived table.
They
are however independent of each other. That's the great things with derived
tables, they are independent from the rest of the query.

If you are on SQL 2005, you might be able to get away with:

SELECT c.company_id, c.company_name,
MAX(ac.due_date ) OVER(PARITION BY c.company_id)
FROM oncd_company c
LEFT JOIN oncd_activity_c ompany ac ON c.company_id = ac.company_id

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx