MAX vs. LAST

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
After creating a formula to extract the 6 right most characters from a 7
digit code and then returning the last one, I found that the results could
vary. After changing the LAST function to the MAX function, I've found the
results to be more consistent. My two questions are:
1. Is MAX the best function to use in this instance and will it continue to
find the right results?
2. Which cases is the LAST function best used in?
Thanks in advance,
Dave
 
Answered embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
After creating a formula to extract the 6 right most characters from a 7
digit code and then returning the last one, I found that the results could
vary. After changing the LAST function to the MAX function, I've found the
results to be more consistent. My two questions are:
1. Is MAX the best function to use in this instance and will it continue
to
find the right results?
Click to expand...

Yes. Max is the function to use to get the highest value, most recent date,
etc.
2. Which cases is the LAST function best used in?
Click to expand...

None. Last exists only as the compliment to First, but it has no practical
use.

First means you want Access to return the first matching value it finds.
Last means that it should load all the matching values, and return the last
one. That has to be the most inefficient approach possible, so it has no
practical use that I know of.
 
Hi,


The practical use of LAST, or of FIRST, is that all the fields that are
aggregated with one of these will become from the SAME record:

f1 f2
99 2
1 3
2 1
3 90


SELECT MIN(f1), MIN(f2)

would return 1 1, but those values are, definitively, not necessary from
the same record. Same if you use MAX. ... or any combination of MIN and MAX
(with the actual data).


SELECT LAST(f1), LAST(f2) would result in any of the record to get
output (not necessary the *latest* one entered in the table). It can be
very useful and, in case you don't have a primary key, ... well, try the
equivalent statement with MS SQL Server... you almost need to use a cursor!
.... now, add a real GROUP BY clause, and you are doomed to out of proportion
complex SQL, (given the nature of the problem) without FIRST / LAST (and
primary key).


Since a record is intended to become part of a group, the record has to be
examined, in cases a SUM( ) or a COUNT(FieldName) is implied, as example,
so, in those situations, LAST( ) should not generate much extra work, since
the record has been read anyhow. Now, if you mean DLast( ), there, I would
be more incline to agree with you. It is there to complement the other
Domains functions, DCount, DSum, ....



Vanderghast, Access MVP
 
Hi Michel

Not sure how practical this is, but I would love to learn.

The example looks like an unsorted recordset, so returning the LAST record
may not be consistent. If it were a sorted recordset, it would make more
sense to me to sort in the opposite order, and user FIRST. Am I missing
something - at the pratical level?
 
Hi,

LAST, and FIRST, do not imply the idea of ordering, neither the idea of
time.

The practical cases are around getting a "typical" record, a "sample"
from the group. As example:

ID tel office
AllenBrowne 122-2312-3345 work
AllenBrowne 122-1991-5665 home


then,

SELECT ID, LAST(tel), LAST(office)
FROM thatTable
GROUP BY ID


can be seen as "picking one" from each GROUP. Sure, in this case, you can
probably say it does not matter if I phone you on what I think is your home
tel number, but is in reality your work tel number, but there are cases
where mixing the info, could generate a problem, and:

SELECT ID, MIN(tel), MIN(office)
FROM thatTable
GROUP BY ID


would probably mix the records (in general).


So, basically, they are useful when you want a "sample" from the group, and
when I say "a" sample I say "one" sample, so, also useful to remove
duplicate (in the sense of keeping one record per group, rather than
getting one record made of a mix of all records, per group). It also insure
you that you get a record, so that you get something valid since in:

SELECT id, LAST(f1), LAST(f2)
FROM ...



it may be from a table with a data validation about f1+f2 BETWEEN 90 and
110

So, using LAST, you still get "something" valid, since, to the contrary of
MIN, you don't "compose" something new, something "out" of record space.


Yep, you are right, they are useful if you use more than ONE of them (if
you look at my examples, they always imply two LAST( ) ). But by extension,
since LAST mean "take a sample", I found myself using it because it is just
more "descriptive" that MIN in some expression where MIN is classically used
to play that same role, ... when there is just ONE field implied. It just
happen that it is MIN that has far less use than LAST, in the end, so, would
be remove MIN ? ... :-) well, I exaggerate a little bit, but not by much.


About my first sentence, their name, FIRST and LAST, derives from the
way these aggregate are working: the first, or last, record SEEN by then
engine, accordingly to the query plan generated at the moment, will be the
record supplying the sample values. So, if the query plan does not use an
index, it may not be linked to an ordering notion, and if the query plan
does not use a table scan, it may not be linked to the notion of "position"
in the table either... but it may... does not matter... all we want is "a"
sample.



Vanderghast, Access MVP
 
Michel Walsh said:
Hi,

LAST, and FIRST, do not imply the idea of ordering, neither the idea of
time.

The practical cases are around getting a "typical" record, a "sample"
from the group. As example:

ID tel office
AllenBrowne 122-2312-3345 work
AllenBrowne 122-1991-5665 home


then,

SELECT ID, LAST(tel), LAST(office)
FROM thatTable
GROUP BY ID


can be seen as "picking one" from each GROUP. Sure, in this case, you can
probably say it does not matter if I phone you on what I think is your home
tel number, but is in reality your work tel number, but there are cases
where mixing the info, could generate a problem, and:

SELECT ID, MIN(tel), MIN(office)
FROM thatTable
GROUP BY ID


would probably mix the records (in general).


So, basically, they are useful when you want a "sample" from the group, and
when I say "a" sample I say "one" sample, so, also useful to remove
duplicate (in the sense of keeping one record per group, rather than
getting one record made of a mix of all records, per group). It also insure
you that you get a record, so that you get something valid since in:

SELECT id, LAST(f1), LAST(f2)
FROM ...



it may be from a table with a data validation about f1+f2 BETWEEN 90 and
110

So, using LAST, you still get "something" valid, since, to the contrary of
MIN, you don't "compose" something new, something "out" of record space.


Yep, you are right, they are useful if you use more than ONE of them (if
you look at my examples, they always imply two LAST( ) ). But by extension,
since LAST mean "take a sample", I found myself using it because it is just
more "descriptive" that MIN in some expression where MIN is classically used
to play that same role, ... when there is just ONE field implied. It just
happen that it is MIN that has far less use than LAST, in the end, so, would
be remove MIN ? ... :-) well, I exaggerate a little bit, but not by much.


About my first sentence, their name, FIRST and LAST, derives from the
way these aggregate are working: the first, or last, record SEEN by then
engine, accordingly to the query plan generated at the moment, will be the
record supplying the sample values. So, if the query plan does not use an
index, it may not be linked to an ordering notion, and if the query plan
does not use a table scan, it may not be linked to the notion of "position"
in the table either... but it may... does not matter... all we want is "a"
sample.



Vanderghast, Access MVP
Click to expand...
 
Kewl post!

Can we hire you as a RAC consultant?:)

I have been thinking about putting FIRST and LAST
in Rac TRANSFORM;
@transform='FIRST(x),LAST(y)'

It seems you are suggesting I can simply pick an arbitrary/random
value of x and y given the grouping.So I might more accurately
define it as ANYFIRST,ANYLAST.Given:
@transform='FIRST(x),LAST(x)' or
@transform='ANYFIRST(x),ANYLAST(x)'

would you be sure that they were different values or would
you allow the same value for both expressions?

Thanks,
steve
www.rac4sql.net
 
Hi,


eh eh eh... ANY destroy the notion of "from the SAME record". I
think once Joe C. ranted about the absence, in the standard, or was it in
implementation, of a ROW aggregate (or ROWSAMPLE, or ROWsomething)
(different than the LIST aggregate), and basically, Joe missed the point
that Jet was delivering it, but under another name. Not that I personally
find the Jet name for these aggregates is the best "move" ever done... since
most people understand them, wrongly, as "latest" record accepted in the
table, or "earliest" record added and still in the table.


I think a possible internal implementation is to make FIRST fill the
"result" when the group is created, and then disregarded the FIRST
aggregate from the "loop", for this group, if the group is ever revisited
again, so FIRST acts only in the constructor of the group; while LAST is
like an UPDATE, meaning that rather than while SUM being result = result +
actualValue, we have LAST as result=actualValue. I think there are cases
where FIRST would be a little bit faster, and some where LAST would be...
but negligible difference, imho.


FIRST and LAST can produce the same value if there is just one
record in the group (trivial) or if records are duplicated over the fields
we aggregate with them. I have got once a nice statement involving elegantly
FIRST and LAST together, but that was a very particular need from a
department of Post Canada. Generally, only one is ever used, either FIRST,
either LAST, in one total query.



Vanderghast, Access MVP
 
Michel Walsh said:
Hi,


eh eh eh... ANY destroy the notion of "from the SAME record". I
think once Joe C. ranted about the absence, in the standard, or was it in
implementation, of a ROW aggregate (or ROWSAMPLE, or ROWsomething)
(different than the LIST aggregate), and basically, Joe missed the point
that Jet was delivering it, but under another name. Not that I personally
find the Jet name for these aggregates is the best "move" ever done... since
most people understand them, wrongly, as "latest" record accepted in the
table, or "earliest" record added and still in the table.


I think a possible internal implementation is to make FIRST fill the
"result" when the group is created, and then disregarded the FIRST
aggregate from the "loop", for this group, if the group is ever revisited
again, so FIRST acts only in the constructor of the group; while LAST is
like an UPDATE, meaning that rather than while SUM being result = result +
actualValue, we have LAST as result=actualValue. I think there are cases
where FIRST would be a little bit faster, and some where LAST would be...
but negligible difference, imho.


FIRST and LAST can produce the same value if there is just one
record in the group (trivial) or if records are duplicated over the fields
we aggregate with them. I have got once a nice statement involving elegantly
FIRST and LAST together, but that was a very particular need from a
department of Post Canada. Generally, only one is ever used, either FIRST,
either LAST, in one total query.
Click to expand...

Michel would you care to write the t-sql code to simulate this?:-)
Now I'm not so sure if it's worth the effort since I could probably
not ever guarantee same/similar behavior as jet.
I do appreciate your thoughts on the subject.

Steve
 
Hi,


If I can use a temp table, then LAST is easy to simulate:



========== JET like syntax
SELECT stor_id, LAST(ord_num), LAST(payterms)
FROM sales
GROUP BY stor_id
==========


would be



=============
USE pubs

GO


SELECT DISTINCT stor_id,
CONVERT( varchar(20), Null) As LASTofOrd_num,
CONVERT( varchar(12), Null) As LASTofPayterms
INTO #temp FROM sales


UPDATE #temp SET
LastOfOrd_num = ord_num,
LASTofPayterms = payterms
FROM sales WHERE sales.stor_id=#temp.stor_id

SELECT * FROM #temp

===============



and, in this "case", the result would be "last" record returned by "SELECT
* FROM sales" (for each stor_id), but that is not important. What is
important is that both fields, LASTofOrd_num and LASTofPayterms can be
tracked to belong to the same original record (with the given stor_id).



FIRST is much more complex, at least, I didn't find an "easy" solution, in
T-SQL, because of the way T-SQL isolates the update with transactions. I
think I have to go with a cursor based solution... far from optimal... I
thought a moment that since both Ord_num and Payterms have a Not NULL
constraint, the following may work:

=============
DROP TABLE #temp

SELECT DISTINCT stor_id,
CONVERT( varchar(20), Null) as FirstofOrd_num,
CONVERT( varchar(12), Null) As FirstOfPayterms
INTO #temp FROM sales

UPDATE #temp
SET
FirstofOrd_num=CASE
WHEN FirstofOrd_num Is Null THEN Ord_num
ELSE FirstofOrd_num END,
FirstOfPayterms=CASE
WHEN FirstOfPayterms Is Null THEN Payterms
ELSE FirstOfPayterms END
FROM sales
WHERE sales.stor_id = #temp.stor_id

================

The Update is based on the assumption that is FirstOfWhatever Is Null, then
no record has yet been in this group, so, take the actual value supplied by
sales, else, forget about any update, but it turns out, because of the
transaction, to return the same result than in the previous case ( and it is
less "generic", since it works only if ALL aggregated fields have a Not Null
constraint). With Jet it would have work... :-)

I continue to look for possibilities to simulate FIRST, with joins or
temporary tables only, but as I see it, I am afraid that I would have to go
for a cursor...



Note that Jet does not return "a" fixed solution, since by its
implementation, the result depends, on the moment, of the query plan. If the
query plan changes, the result of LAST, or of FIRST, may change. That is why
some unaware people sometimes describe the "result" of LAST or of FIRST as
"unreliable".


Vanderghast, Access MVP
 
Thanks for the lesson Michel.But I think it's to much
of an iffy thing (at least at this point:).Adding a new
FIRST/LAST may confuse users even more than they
are already confused by what is meant by FIRST/LAST
in RAC:).RAC follows the sql99 Oracle FIRST/LAST
idea (also found in SAS's FIRST.value/LAST.value).
Here FIRST/LAST is guaranteed to be deterministic
since (in sql99 speak) it's defined in a partition (ala a
GROUP BY and an ORDER BY.Of course here we are
NOT talking of aggregate functions but of specific row values
based on grouping.Perhaps an aggregate FIRST/LAST such as
described in:
http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html

would be much simpler and easier to understand for users:)

Best,
Steve
www.rac4sql.net
 
Back
Top