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_date)
FROM oncd_activity_company AS actcomp, oncd_activity AS act
WHERE actcomp.activity_id = act.activity_id
GROUP BY company_id

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

SELECT oncd_company.company_id,
oncd_company.company_name,
act.due_date
FROM oncd_company
LEFT OUTER JOIN oncd_activity_company ON (oncd_company.company_id =
oncd_activity_company.company_id)
LEFT OUTER JOIN (SELECT actcomp.company_id, MAX(act.due_date)

FROM oncd_activity_company AS actcomp, oncd_activity AS act

WHERE actcomp.activity_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********@broadreachpartnersinc.com) writes:

LEFT OUTER JOIN (SELECT actcomp.company_id, MAX(act.due_date)

FROM oncd_activity_company AS actcomp, oncd_activity AS act

WHERE actcomp.activity_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_company ac ON c.company_id = ac.company_id
LEFT JOIN (SELECT ac.company_id, due_date = MAX(act.due_date)
FROM oncd_activity_company 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_company ac ON c.company_id = ac.company_id

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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