Problem With FIRST () Agregate function

mitulkoradia · January 20th, 2007, 02:55 AM

hi

can any one help me to find the solution on my following problem

i am creating one Crostab query like done in ms Access. but there is one problem.

i have to use. FIRST() Aggregate function like SUM() AVG()

you can find help for that function from here..

http://www.w3schools.com/sql/sql_functions.asp

it says the FIRST Function is not supported in SQL Server 2000..

i want the same functionality as First() give. (First Row's Specified value of Group).

here i am giving you my sample query.

Code:

SELECT  qryRisk_Matrix.SystemName, qryRisk_Matrix.SAPFunctionalLocation, qryRisk_Matrix.SystemID, qryRisk_Matrix.BusinessName, 
                      qryRisk_Matrix.RegionName, qryRisk_Matrix.SchemeName, qryRisk_Matrix.CMSID, qryCapProjCount.CountOfProjectID, 
                      qrySwppProjCount.SwppProjCount,
                      'W-WQ' =FIRST(CASE RiskEventRef WHEN 'W-WQ' THEN qryRisk_Matrix.Risk0 END), 
                      'W-NH' = FIRST(CASE RiskEventRef WHEN 'W-NH' THEN qryRisk_Matrix.Risk0 END) FROM         qrySwppProjCount INNER JOIN
                      (qryCapProjCount INNER JOIN
                      qryRisk_Matrix ON qryCapProjCount.SystemID = qryRisk_Matrix.SystemID) ON qrySwppProjCount.SystemID = qryRisk_Matrix.SystemID
WHERE     (((qryRisk_Matrix.BusinessName) = 'Regulated Water'))
GROUP BY qryRisk_Matrix.SystemName, qryRisk_Matrix.SAPFunctionalLocation, qryRisk_Matrix.SystemID, qryRisk_Matrix.BusinessName, 
                      qryRisk_Matrix.RegionName, qryRisk_Matrix.SchemeName, qryRisk_Matrix.CMSID, qryCapProjCount.CountOfProjectID, 
                      qrySwppProjCount.SwppProjCount

Mitul N Koradia