[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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

0
stacydr
Asked:
stacydr
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now