Sql 2005 Max Statement Needed

Hi-
I am creating a view in Sql Server 2005 Managment Studio.
I have three tables.
Company Table
Activity Table
Activity-Company Table

What I want is one line item per company - returning the Activity Row that has the MAX due date.
This I have accomplished.. But I have some acttivities that have the SAME de date -- because of an system and a timestamp was not placed with the date.

So I want to take the code I am attaching below and add more code that then takes the return from above and where there are multiple rows per company -- I want the row that have the MAX Activity ID ...

Do-able??
SELECT
dbo.oncd_company.company_id, 
dbo.oncd_company.company_name_1, 
dbo.oncd_activity.due_date, 
dbo.oncd_activity_note.note, 
dbo.oncd_activity_company.activity_id AS act_id
FROM dbo.oncd_company 
LEFT OUTER JOIN dbo.oncd_activity_company ON dbo.oncd_company.company_id = dbo.oncd_activity_company.company_id
LEFT OUTER JOIN(SELECT actcomp.company_id, MAX(act.due_date) AS due_date
                FROM  dbo.oncd_activity_company AS actcomp INNER JOIN
                dbo.oncd_activity  AS act ON actcomp.activity_id = act.activity_id
                GROUP BY actcomp.company_id) AS act_1 ON dbo.oncd_activity_company.company_id = act_1.company_id
LEFT OUTER JOIN dbo.oncd_activity ON dbo.oncd_activity.activity_id = dbo.oncd_activity_company.activity_id AND 
                      dbo.oncd_activity.due_date = act_1.due_date 
LEFT OUTER JOIN dbo.oncd_activity_note ON dbo.oncd_activity_note.activity_id = dbo.oncd_activity.activity_id
WHERE     (act_1.due_date = dbo.oncd_activity.due_date) OR (dbo.oncd_activity_company.activity_id IS NULL)						         
ORDER BY dbo.oncd_company.company_name_1

Open in new window

LVL 2
stacydrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
See if this gets you the results you are looking for.  It uses an OVER statement to produce row numbers by company id based on the due date in descending order.


;WITH cte AS (
SELECT
dbo.oncd_company.company_id, 
dbo.oncd_company.company_name_1, 
dbo.oncd_activity.due_date, 
dbo.oncd_activity_note.note, 
dbo.oncd_activity_company.activity_id AS act_id,
row_number() OVER (PARTITION BY dbo.oncd_company.company_id ORDER BY dbo.oncd_activity.due_date DESC) AS rank
FROM dbo.oncd_company 
LEFT OUTER JOIN dbo.oncd_activity_company ON dbo.oncd_company.company_id = dbo.oncd_activity_company.company_id
LEFT OUTER JOIN(SELECT actcomp.company_id, MAX(act.due_date) AS due_date
                FROM  dbo.oncd_activity_company AS actcomp INNER JOIN
                dbo.oncd_activity  AS act ON actcomp.activity_id = act.activity_id
                GROUP BY actcomp.company_id) AS act_1 ON dbo.oncd_activity_company.company_id = act_1.company_id
LEFT OUTER JOIN dbo.oncd_activity ON dbo.oncd_activity.activity_id = dbo.oncd_activity_company.activity_id AND 
                      dbo.oncd_activity.due_date = act_1.due_date 
LEFT OUTER JOIN dbo.oncd_activity_note ON dbo.oncd_activity_note.activity_id = dbo.oncd_activity.activity_id
WHERE     (act_1.due_date = dbo.oncd_activity.due_date) OR (dbo.oncd_activity_company.activity_id IS NULL)						         
ORDER BY dbo.oncd_company.company_name_1
)
SELECT *
FROM cte
WHERE rank = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tigin44Commented:
SELECT
dbo.oncd_company.company_id,
dbo.oncd_company.company_name_1,
dbo.oncd_activity.due_date,
dbo.oncd_activity_company.activity_id AS act_id
FROM dbo.oncd_company
      LEFT OUTER JOIN dbo.oncd_activity_company ON dbo.oncd_company.company_id = dbo.oncd_activity_company.company_id
      LEFT OUTER JOIN (SELECT TOP 1 act.activity_id, MAX(act.due_date) AS due_date
                                    FROM  dbo.oncd_activity act
                                    GROUP BY act.activity_id
                                ORDER BY act.activity_id DESC) act ON dbo.oncd_activity_company.activity_id = act.activity_id AND dbo.oncd_activity_company.due_date = act.due_date
0
stacydrAuthor Commented:
mwvisa1-
Great it does work -- but the last Order By Statement I had -- line 19

ORDER BY dbo.oncd_company.company_name_1

I had to remove because I got an error..

Msg 1033, Level 15, State 1, Line 20
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Anyway to get the Order By statement in too... not mission critical but really nice to have..
0
stacydrAuthor Commented:
thanks so much! I can work it even though I have to forgo the company name order by....
0
Kevin CrossChief Technology OfficerCommented:
Yes, sorry, you either have to put SELECT TOP 100 PERCENT inside the WITH (common table expression) or just do this:
;WITH cte AS (
SELECT
dbo.oncd_company.company_id, 
dbo.oncd_company.company_name_1, 
dbo.oncd_activity.due_date, 
dbo.oncd_activity_note.note, 
dbo.oncd_activity_company.activity_id AS act_id,
row_number() OVER (PARTITION BY dbo.oncd_company.company_id ORDER BY dbo.oncd_activity.due_date DESC) AS rank
FROM dbo.oncd_company 
LEFT OUTER JOIN dbo.oncd_activity_company ON dbo.oncd_company.company_id = dbo.oncd_activity_company.company_id
LEFT OUTER JOIN(SELECT actcomp.company_id, MAX(act.due_date) AS due_date
                FROM  dbo.oncd_activity_company AS actcomp INNER JOIN
                dbo.oncd_activity  AS act ON actcomp.activity_id = act.activity_id
                GROUP BY actcomp.company_id) AS act_1 ON dbo.oncd_activity_company.company_id = act_1.company_id
LEFT OUTER JOIN dbo.oncd_activity ON dbo.oncd_activity.activity_id = dbo.oncd_activity_company.activity_id AND 
                      dbo.oncd_activity.due_date = act_1.due_date 
LEFT OUTER JOIN dbo.oncd_activity_note ON dbo.oncd_activity_note.activity_id = dbo.oncd_activity.activity_id
WHERE     (act_1.due_date = dbo.oncd_activity.due_date) OR (dbo.oncd_activity_company.activity_id IS NULL)		        
)
SELECT *
FROM cte
WHERE rank = 1
ORDER BY company_name_1

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.