Access's LAST aggregate function equivalent

Hi,
I am porting an Access application that uses the LAST
aggregate function to SQL Server 7.0. I would like to know
if there is any equivalent to the LAST function in SQL
Server 7.0. I've been looking in the documentation and I
haven't found anything. The problem I'm running into is
that I have to use a LAST-kind of function since the last
record in the group doesn't have anything else to locate
it by. It may be the MAX record, but there are cases where
it won't be. I also can't use SUM, since that last record
is already a SUM of all previous records in that group. If
anyone can help, it would be greatly appreciated.
Thanks in advance,
StephanHello Stephan !
Just post your TSQL code so we can post you how you could avoid using LAST.
(Also DDl, sample data and estimated query result would be helpful)
Jens Süßmeyer.
"Stephan Desmoulin" <sdesmoulin@.huimfg.com> schrieb im Newsbeitrag
news:029f01c37952$cc57a140$a101280a@.phx.gbl...
> Hi,
> I am porting an Access application that uses the LAST
> aggregate function to SQL Server 7.0. I would like to know
> if there is any equivalent to the LAST function in SQL
> Server 7.0. I've been looking in the documentation and I
> haven't found anything. The problem I'm running into is
> that I have to use a LAST-kind of function since the last
> record in the group doesn't have anything else to locate
> it by. It may be the MAX record, but there are cases where
> it won't be. I also can't use SUM, since that last record
> is already a SUM of all previous records in that group. If
> anyone can help, it would be greatly appreciated.
> Thanks in advance,
> Stephan|||Unfortunately, I can't post expected results since the
data I'm running off of in SQL Server is several months
older than the data in Access. The SQL statement looks
like this:
SET DATEFIRST 6
SELECT fldBHSCustID, MAX(fldBHSRunDate), LAST
(fldBHSExtPrice)
FROM tblBklgHistSum
GROUP BY fldBHSCustID, DATEADD(d, 7 - DATEPART(dw,
fldBHSRunDate), fldBHSRunDate)
ORDER BY fldBHSCustID
The calculation with the dates is used to group the
results in one-week chunks with the week end date always
being a Friday. Note that the LAST function isn't actually
there in my query, it's what I would like to use.
The data kind of looks like this:
CustID RunDate ExtPrice
-- -- --
1 9/9/2003 123
1 9/10/2003 123
1 9/11/2003 123
1 9/12/2003 123
2 9/11/2003 450
2 9/12/2003 423
3 9/10/2003 12
3 9/11/2003 45
3 9/11/2003 64
3 9/12/2003 78
The trouble as this sample data shows, is that the
ExtPrice field is fairly random and I only want to use the
very last value entered.
Thanks for the help,
Stephan
>--Original Message--
>Hi,
>I am porting an Access application that uses the LAST
>aggregate function to SQL Server 7.0. I would like to
know
>if there is any equivalent to the LAST function in SQL
>Server 7.0. I've been looking in the documentation and I
>haven't found anything. The problem I'm running into is
>that I have to use a LAST-kind of function since the last
>record in the group doesn't have anything else to locate
>it by. It may be the MAX record, but there are cases
where
>it won't be. I also can't use SUM, since that last record
>is already a SUM of all previous records in that group.
If
>anyone can help, it would be greatly appreciated.
>Thanks in advance,
>Stephan
>.
>|||This is a nice example of one of the problems with the "LAST aggregate". You
didn't tell us the primary key but it would appear that there can be more
than one "last" extprice (because your table allows more than one price on
the same Rundate). If that is the case which price do you choose? The
following query gives the MAX, LAST extprice.
Tip: You can use the DATEDIFF function as shown to avoid any dependency on
the DATEFIRST setting.
Another tip: Get rid of those horrid "tbl" and "fld" prefixes. SQL has
columns not fields. And we know it's a column because it's in a table!
CREATE TABLE tblBklgHistSum (custid INTEGER, rundate DATETIME, extprice
INTEGER, PRIMARY KEY (custid, rundate, extprice))
INSERT INTO tblBklgHistSum VALUES (1, '20030909', 123)
INSERT INTO tblBklgHistSum VALUES (1, '20030910', 123)
INSERT INTO tblBklgHistSum VALUES (1, '20030911', 123)
INSERT INTO tblBklgHistSum VALUES (1, '20030912', 123)
INSERT INTO tblBklgHistSum VALUES (2, '20030911', 450)
INSERT INTO tblBklgHistSum VALUES (2, '20030912', 423)
INSERT INTO tblBklgHistSum VALUES (3, '20030910', 12)
INSERT INTO tblBklgHistSum VALUES (3, '20030911', 45)
INSERT INTO tblBklgHistSum VALUES (3, '20030911', 64)
INSERT INTO tblBklgHistSum VALUES (3, '20030912', 78)
SELECT T.custid, T.rundate,
MAX(T.extprice) AS extprice
FROM tblBklgHistSum AS T
JOIN
(SELECT custid,
MAX(rundate) AS rundate
FROM tblBklgHistSum
GROUP BY custid,
FLOOR(DATEDIFF(DAY,'20000101',rundate)/7)) AS M
ON T.custid = M.custid AND T.rundate = M.rundate
GROUP BY T.custid, T.rundate
--
David Portas
--
Please reply only to the newsgroup
--