469,323 Members | 1,397 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Return Max Value from SubQuery

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.

Apr 21 '07 #1
1 11114
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
Apr 21 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Chris H | last post: by
9 posts views Thread by lenygold via DBMonster.com | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.