LAST() function ? - Olaf, you there?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Webbiz - 26 Jun 2009 16:20 GMT

What happened to good ole' Olaf? Been missing.

Here is an SQL statement Olaf helped me with:

SQLString = "SELECT
CLng(Last(Month,ID))||'/'||CLng(Last(Day,ID))||'/'||Year as Date, " &
_
"Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0
as IsSwingBtm1, 0 as IsSwingTop2, " & _
"0 as IsSwingBtm2, Null as Delta1, Null as Delta2, " &
_
"0 as Offset1, 0 as Offset2 FROM [" & sTable & "]
GROUP BY Year, Month, Day"

I did a search on the net for the Last() function in SQL, and all I
can find is the Last() function that takes only one parameter.

Where can I find the info on the Last() function I'm using here that
has two parameters?

DB is SQLite and I'm using Olaf's vb wrapper on this.

Thanks.

Webbiz

Reply to this Message

Schmidt - 02 Jul 2009 21:31 GMT

Just found that post in google.groups - it is not showing
up in my newsreader (together with your two other
questions, which you already solved on your own).

> I did a search on the net for the Last() function in SQL, and all I
> can find is the Last() function that takes only one parameter.

These single-param First()/Last()-functions rely on a certain
"incoming order" of each (about to be grouped) single record
(usually requiring an explicite Order By Clause then, which ensures
the correct "incoming-order" of the Sub-Records in each Group).

> Where can I find the info on the Last() function I'm using here
> that has two parameters?

The First()/Last()-AggregateFunctions in my Wrapper are able
to avoid an explicit Order By enhancement in your query, since
they do incorporate an additional Field (given in the second
Parameter) which provides the "order-criterion" in itself.

If you use ID as the second param (which filled in beforehand
into the stocktable in ascending order, for each inserted "next-day-
entry" in the import-routine), then I can make good use of that
Helper-Param in each Sub-Grouping for the First() and Last()
Aggregate-Functions - performing better in that case, than an
explicitely given Order By Clause.
The First() Entry in a SubGroup-Aggregate is the one, which
is *paired* with the lowest value of the second (order-criterion)
parameter (within that SubGroup).
The Last() Entry in a SubGroup-Aggregate is the one, which
is *paired* with the highest value of the second (order-criterion)
parameter (within that SubGroup).
Both not relying on a certain "incoming-order" of all the sub-
members which belong to a group (which you usually define
over the Group By Clause).

HTH

Olaf

Reply to this Message

VB Forum / Database Access / July 2009

LAST() function ? - Olaf, you there?