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.
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.QuoteCodeFROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectIDWHERE 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.QuoteCodeFROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectIDINNER JOIN ( SELECT ProjectID ,Max(QuoteDate) AS QuoteDate FROM QUOTE GROUP BY ProjectID ) AS Q ON QUOTE.ProjectID = Q.ProjectID AND QUOTE.QuoteDate = Q.QuoteDate |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-16 : 05:07:14
|
[code]SELECT p.*, q.QuoteDate, q.QuoteCodeFROM PROJECT pLEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY QuoteDate DESC) AS Rn,*FROM QUOTE)q ON p.ProjectID = q.ProjectIDAND q.Rn = 1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-01-16 : 07:22:58
|
WITH CTE AS(SELECT PROJECT.* ,QUOTE.QuoteDate ,QUOTE.QuoteCodeFROM PROJECT INNER JOINON QUOTE.ProjectID = Q.ProjectIDAND 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 = 1P.V.P.MOhan |
|
|
|
|
|
|
|