 | |
#1
 
| |||
| |||
|
Agreggate and Scalar Combination -
07-19-2004
, 02:15 PM
#2
| |||
| |||
|
|
I have a table that tracks projects and funding sources. The columns are as follows: 1. UPN (Universal Project Number) (PK) (FK-Constraint to Projects) 2. Funding Source Type (PK) (FK-Constraint to FundSourceTypes) 3. Stage Type (PK) (FK_Constraint to Stages) 4. Amount 5. Date Each project may have multiple funding source types, and each funding source type has 4 stages that they go through (thus, the composite primary key). History is tracked, so the stage type is not updated, a new row is added. I need to return the amount for the most recent stage of each funding source for each UPN. The stages are in order, so the MAX function should work. select UPN, TC_SourceID, MAX(TC_StageID) FROM TC_Versions GROUP BY UPN, TC_SourceID Returns the most recent stage for each source of each UPN. All fine and dandy. However, I also need to return the value in the amount column of each row returned by the above query. Adding amount to the query doesnt work, because it has to either be part of an aggregate or in the GROUP BY to be in the select list. It is possible for the amount to change at each stage so grouping by amount doesnt work. Any ideas? Thank you in advance. |
#3
| |||
| |||
|
|
I have a table that tracks projects and funding sources. The columns are as follows: 1. UPN (Universal Project Number) (PK) (FK-Constraint to Projects) 2. Funding Source Type (PK) (FK-Constraint to FundSourceTypes) 3. Stage Type (PK) (FK_Constraint to Stages) 4. Amount 5. Date Each project may have multiple funding source types, and each funding source type has 4 stages that they go through (thus, the composite primary key). History is tracked, so the stage type is not updated, a new row is added. I need to return the amount for the most recent stage of each funding source for each UPN. The stages are in order, so the MAX function should work. select UPN, TC_SourceID, MAX(TC_StageID) FROM TC_Versions GROUP BY UPN, TC_SourceID Returns the most recent stage for each source of each UPN. All fine and dandy. However, I also need to return the value in the amount column of each row returned by the above query. Adding amount to the query doesnt work, because it has to either be part of an aggregate or in the GROUP BY to be in the select list. It is possible for the amount to change at each stage so grouping by amount doesnt work. Any ideas? Thank you in advance. |
select UPN, TC_SourceID, amount
FROM TC_Versions V
WHERE TC_StageID = (
SELECT MAX(TC_StageID) from TC_Versions
WHERE UPN = V.UPN
AND TC_SourceID = V.TC_SourceID
)
HTH
/Lennart
#4
| |||
| |||
|
|
anthony (AT) poeticcode (DOT) net (Anthony Antognoli) wrote in message news:<ef55064b.0407191115.6b8f2740 (AT) posting (DOT) google.com>... I have a table that tracks projects and funding sources. The columns are as follows: 1. UPN (Universal Project Number) (PK) (FK-Constraint to Projects) 2. Funding Source Type (PK) (FK-Constraint to FundSourceTypes) 3. Stage Type (PK) (FK_Constraint to Stages) 4. Amount 5. Date Each project may have multiple funding source types, and each funding source type has 4 stages that they go through (thus, the composite primary key). History is tracked, so the stage type is not updated, a new row is added. I need to return the amount for the most recent stage of each funding source for each UPN. The stages are in order, so the MAX function should work. select UPN, TC_SourceID, MAX(TC_StageID) FROM TC_Versions GROUP BY UPN, TC_SourceID Returns the most recent stage for each source of each UPN. All fine and dandy. However, I also need to return the value in the amount column of each row returned by the above query. Adding amount to the query doesnt work, because it has to either be part of an aggregate or in the GROUP BY to be in the select list. It is possible for the amount to change at each stage so grouping by amount doesnt work. Any ideas? Thank you in advance. Something like: select UPN, TC_SourceID, amount FROM TC_Versions V WHERE TC_StageID = ( SELECT MAX(TC_StageID) from TC_Versions WHERE UPN = V.UPN AND TC_SourceID = V.TC_SourceID ) HTH /Lennart |
#5
| |||
| |||
|
|
That is indeed it. Thank you both very much. This is a correlated subquery, correct? |
#6
| |||
| |||
|
|
That is indeed it. Thank you both very much. This is a correlated subquery, correct? |
/L
[...]
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.