First(), Last(0 function

Hello,
I have this query in access and I woul like to convert it to SQL.
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
-Scott
Scott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>
|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
returns
>
|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> returns
>