SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Aggregate function in Subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tyguy14
Starting Member

1 Posts

Posted - 01/15/2014 :  14:13:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/15/2014 :  14:29:58  Show Profile  Reply with Quote
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

Edited by - Lamprey on 01/15/2014 14:31:31
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/16/2014 :  05:07:14  Show Profile  Reply with Quote

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



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

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 01/16/2014 :  07:22:58  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.26 seconds. Powered By: Snitz Forums 2000