Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Aggregate function in Subquery

Author  Topic 

tyguy14
Starting Member

1 Post

Posted - 2014-01-15 : 14:13:22
Hello,

I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.

So in this case I am using this syntax to show the most recent quote within the project.

    SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);

My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.

Let me know if this isn't clear.

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-15 : 14:29:58
Two things,

One, to solve that issue you can turn that sub-query into a derived-table/inline view.
Second, since you applying a predicate to a subordinate table (Quote), you have, affectively, turned that query into an INNER JOIN. I'm not sure that's what you intended or not.

I'm pretty sure this WON'T work, but maybe it'll give you an idea:
SELECT 
PROJECT.*
,QUOTE.QuoteDate
,QUOTE.QuoteCode
FROM
PROJECT
LEFT JOIN
QUOTE
ON PROJECT.ProjectID = QUOTE.ProjectID
INNER JOIN
(
SELECT
ProjectID
,Max(QuoteDate) AS QuoteDate
FROM
QUOTE
GROUP BY
ProjectID
) AS Q
ON QUOTE.ProjectID = Q.ProjectID
AND QUOTE.QuoteDate = Q.QuoteDate
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-16 : 05:07:14
[code]
SELECT p.*, q.QuoteDate, q.QuoteCode
FROM PROJECT p
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY QuoteDate DESC) AS Rn,*
FROM QUOTE)q ON p.ProjectID = q.ProjectID
AND q.Rn = 1
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2014-01-16 : 07:22:58
WITH CTE AS(
SELECT
PROJECT.*
,QUOTE.QuoteDate
,QUOTE.QuoteCode
FROM
PROJECT
INNER JOIN
ON QUOTE.ProjectID = Q.ProjectID
AND QUOTE.QuoteDate = Q.QuoteDate

)

SELECT *, ROW_NUMBER() OVER (PARTITION BY QQ.ProjectID ORDER BY QQ.QuoteDate DESC) AS Rn FROM CTE C
INNER JOIN QUOTE QQ
ON QQ.ProjectID = C.ProjectID
WHERE QQ.RN = 1

P.V.P.MOhan
Go to Top of Page
   

- Advertisement -