Minimum value without using ROW_NUMBER

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

SQL Server 2005 Forums

Transact-SQL (2005)

Minimum value without using ROW_NUMBER

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-27 : 11:17:57

Hi Team,

I am trying to achieve the following without using ROW_NUMBER or any other ranking function. I know that there won’t be much performance impact because of the use of ranking function; still I want to avoid them for learning purpose.

Scenario: There can be multiple cancellations for each FundingID. I want to select the FundingCancellationReason corresponding to minimum date for each funding.

I wrote a query as follows. It resulted in a SQL error

CREATE TABLE #FundingCancellation(
[FundingCancellationID] INT IDENTITY(1,1) NOT NULL,
[FundingID] INT ,
FundingCancellationDt SMALLDATETIME ,
FundingCancellationReason VARCHAR(50)
)

SELECT FundingID,
MIN(FundingCancellationDt),
( SELECT FundingCancellationReason
FROM #FundingCancellation FC2
WHERE FC1.FundingID = FC2.FundingID
AND FC2.FundingCancellationDt = MIN(FundingCancellationDt)
) [Reason Corresponding Minimum Date]
FROM #FundingCancellation FC1
GROUP BY FundingID

-- -- An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Could you please help me to avoid the SQL Error?

Thanks
Lijo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 11:23:41

[code]
SELECT fc.*
FROM #FundingCancellation fc
INNER JOIN (SELECT [FundingID],MIN(FundingCancellationDt) AS First
FROM #FundingCancellation
GROUP BY [FundingID]) fc1
ON fc1.[FundingID]=fc.[FundingID]
AND fc1.First = fc.FundingCancellationDt
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources