SELECT clause and LAST function causing problems

[Jean]

Hello all,
I have the following data, that was queried and sorted to columns
PROBLEM_ID and then by STATUSDATE (ascending):

STATUS_ID STATUSDATE PROBLEM_ID
--------- ---------- ----------
10 12/04/2005 1234
40 15/05/2005 1234
10 11/11/2004 1235
50 15/12/2004 1235
70 20/01/2005 1235

I now want another query which returns the row which is always the
latest STATUSDATE. This is what I have done so far:

SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS
FROM qryFirst
GROUP BY qryFirst.PROBLEM_ID;

However, when I want to add qryFirst.STATUSDATE to the SELECT clause, I
get an error ,,You tried to execute a query that does not include the
specified expression <name> as part of an aggregate function. (Error
3122)"

I need to see what the STATUSDATE is for each of the records returned
in the second query. How do I do this? Please!!!!

Kind Regards,

Jean

[Tom van Stiphout]

On 19 Jul 2005 04:26:29 -0700, "Jean" <je**********@hotmail.com>
wrote:

Classic problem. I always solve it by using two queries. The first one
(let's call it Q2) gets the Max(StatusDate) group by ProblemID.
The second one joins the original query Q1 and Q2 by both the
StatusDate and ProblemID fields, and then you can additionally select
StatusID and any other column Q1 might have.

Btw, note the use of Max rather than Last. I find it more predictable,
and it is more universally part of the SQL language.

-Tom.

Hello all,
I have the following data, that was queried and sorted to columns
PROBLEM_ID and then by STATUSDATE (ascending):

STATUS_ID STATUSDATE PROBLEM_ID
--------- ---------- ----------
10 12/04/2005 1234
40 15/05/2005 1234
10 11/11/2004 1235
50 15/12/2004 1235
70 20/01/2005 1235

I now want another query which returns the row which is always the
latest STATUSDATE. This is what I have done so far:

SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS
FROM qryFirst
GROUP BY qryFirst.PROBLEM_ID;

However, when I want to add qryFirst.STATUSDATE to the SELECT clause, I
get an error ,,You tried to execute a query that does not include the
specified expression <name> as part of an aggregate function. (Error
3122)"

I need to see what the STATUSDATE is for each of the records returned
in the second query. How do I do this? Please!!!!

Kind Regards,

Jean

[Steve Jorgensen]

On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout <no*************@cox.net>
wrote:

On 19 Jul 2005 04:26:29 -0700, "Jean" <je**********@hotmail.com>
wrote:

Classic problem. I always solve it by using two queries. The first one
(let's call it Q2) gets the Max(StatusDate) group by ProblemID.
The second one joins the original query Q1 and Q2 by both the
StatusDate and ProblemID fields, and then you can additionally select
StatusID and any other column Q1 might have.

Btw, note the use of Max rather than Last. I find it more predictable,
and it is more universally part of the SQL language.

To add to what Tom said, the First() and Last() functions are unfortunately
named. The thing either of them does is to return an arbitrary value from the
group, with nothing particularly "first" or "last" about it. I presume these
functions actually do something like what their names imply if you are
querying a table linked to an ISAM data source in which physical order is
considered meaningful, such as Paradox.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:qk********************************@4ax.com:

On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On 19 Jul 2005 04:26:29 -0700, "Jean" <je**********@hotmail.com>
wrote:

Classic problem. I always solve it by using two queries. The first
one (let's call it Q2) gets the Max(StatusDate) group by
ProblemID. The second one joins the original query Q1 and Q2 by
both the StatusDate and ProblemID fields, and then you can
additionally select StatusID and any other column Q1 might have.

Btw, note the use of Max rather than Last. I find it more
predictable, and it is more universally part of the SQL language.

To add to what Tom said, the First() and Last() functions are
unfortunately named. The thing either of them does is to return
an arbitrary value from the group, with nothing particularly
"first" or "last" about it. I presume these functions actually do
something like what their names imply if you are querying a table
linked to an ISAM data source in which physical order is
considered meaningful, such as Paradox.

Well, First() means the first value for that column in the recordset
in its present order, Last() means the last value.

It's no different than the fact that TOP 10 on a query sorted in
descending data order will return a different set of records than
TOP 10 on the same query sorted by CompanyName ascending.

It should be self-evident that the terms First() and Last() are
dependent on the ordering of the domain to which they are applied.
Why this confuses people into saying that they return
non-predictable results, I can't say. It's only non-predictable if
you're ignoring the sort order.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Tue, 19 Jul 2005 14:16:52 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:qk********************************@4ax.com :

On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On 19 Jul 2005 04:26:29 -0700, "Jean" <je**********@hotmail.com>
wrote:

Classic problem. I always solve it by using two queries. The first
one (let's call it Q2) gets the Max(StatusDate) group by
ProblemID. The second one joins the original query Q1 and Q2 by
both the StatusDate and ProblemID fields, and then you can
additionally select StatusID and any other column Q1 might have.

Btw, note the use of Max rather than Last. I find it more
predictable, and it is more universally part of the SQL language.

To add to what Tom said, the First() and Last() functions are
unfortunately named. The thing either of them does is to return
an arbitrary value from the group, with nothing particularly
"first" or "last" about it. I presume these functions actually do
something like what their names imply if you are querying a table
linked to an ISAM data source in which physical order is
considered meaningful, such as Paradox.

Well, First() means the first value for that column in the recordset
in its present order, Last() means the last value.

It's no different than the fact that TOP 10 on a query sorted in
descending data order will return a different set of records than
TOP 10 on the same query sorted by CompanyName ascending.

It should be self-evident that the terms First() and Last() are
dependent on the ordering of the domain to which they are applied.
Why this confuses people into saying that they return
non-predictable results, I can't say. It's only non-predictable if
you're ignoring the sort order.

It is, in fact self-evident until you realize there's no way to specify the
sort that you're talking about. Aggregate functions, including First() and
Last() return an item from within the group, but you can only sort a query on
fields on the group by clause or on aggregate results.

For example, let's say I have the following...

tblPerson
PersonID
FullName
StreetAddress
City
State
ZipCode

Now, let's say I want the first full name in alphabetical order for each Zip
code...

SELECT ZipCode,
First(FullName) As FirstFullName
FROM tblPerson
GROUP BY ZipCode
ORDER BY ZipCode

So - what does First() mean? If you say that it should be in the sort order,
well, the sort order only sorts ZipCode groups. If I try to add FullName to
the sort, that's an error because FullName is not part of the GROUP BY. If I
try to solve that by adding FullName to the GROUP BY, that of course, defeats
the whole purpose because I no longer have a single group for each ZipCode.
There is simply no way in SQL to specify a sort order by which First or Last
within a group should be identified.

[Jean]

Thanks for the input guys, and for clearing that up with the FIRST and
LAST functions.

I actually tried FIRST/LAST in some way and got it right, but then I
had to use the FIRST function instead of the LAST. Having got it right
I thought cool, but WHY FIRST???

So I tried MAX as you suggested, and it still works.

Tom, I tried your suggestion, and compared it to mine, and it gives the
same results. Thanks.
This is what I did:

SELECT qryFirst.PROBLEM_ID, MAX(qryFirst.STATUS_ID) AS LAST_STATUS
FROM qryFirst
GROUP BY qryFirst.PROBLEM_ID;

I will use your second query suggestion now to get the STATUSDATE.

Regards,

Jean

[Bri]

Steve Jorgensen wrote:

On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout <no*************@cox.net>
wrote:
To add to what Tom said, the First() and Last() functions are unfortunately
named. The thing either of them does is to return an arbitrary value from the
group, with nothing particularly "first" or "last" about it. I presume these
functions actually do something like what their names imply if you are
querying a table linked to an ISAM data source in which physical order is
considered meaningful, such as Paradox.

I thought that if the source was sorted then you got the Min or Max?

ie, these are equivalent:
SELECT Max(Value) AS A FROM MTable
SELECT Last(Value) AS A FROM [SELECT * FROM MTable ORDER BY Value]. AS S

From this it is obviously easier to use the Max as without the Sort,
Last is not goin to work predictably.

My understanding was that in a newly compacted DB that the tables were
physically sorted by the PK, so there is an order. Of course, as soon as
you start adding/deleting/editing that the order is no longer guaranteed.

Personally, I only use First or Last when I need to include a field in
an Aggregate/Transform query that isn't part of the
Grouping/Count/Sum/etc.

--
Bri

[Steve Jorgensen]

On Tue, 19 Jul 2005 16:23:21 GMT, Bri <no*@here.com> wrote:

Steve Jorgensen wrote:

On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout <no*************@cox.net>
wrote:
To add to what Tom said, the First() and Last() functions are unfortunately
named. The thing either of them does is to return an arbitrary value from the
group, with nothing particularly "first" or "last" about it. I presume these
functions actually do something like what their names imply if you are
querying a table linked to an ISAM data source in which physical order is
considered meaningful, such as Paradox.
I thought that if the source was sorted then you got the Min or Max?

ie, these are equivalent:
SELECT Max(Value) AS A FROM MTable
SELECT Last(Value) AS A FROM [SELECT * FROM MTable ORDER BY Value]. AS S

That was not my impression, but I have never tested it. Supposedly, the
sorting of any source is ignored when it is used in the FROM clause of another
query. In fact, you can see this at work if you just save one query with a
sort, then run a query of that query with no sort. The output generally looks
the same as doing a single query with no sort. Also, what would the order of
a FROM source mean if the FROM clause is a join of multiple sources? Which
sort would be definitive?

From this it is obviously easier to use the Max as without the Sort,
Last is not goin to work predictably.

My understanding was that in a newly compacted DB that the tables were
physically sorted by the PK, so there is an order. Of course, as soon as
you start adding/deleting/editing that the order is no longer guaranteed.

Personally, I only use First or Last when I need to include a field in
an Aggregate/Transform query that isn't part of the
Grouping/Count/Sum/etc.

[Bri]

Steve Jorgensen wrote:

I thought that if the source was sorted then you got the Min or Max?

ie, these are equivalent:
SELECT Max(Value) AS A FROM MTable
SELECT Last(Value) AS A FROM [SELECT * FROM MTable ORDER BY Value]. AS S

That was not my impression, but I have never tested it. Supposedly, the
sorting of any source is ignored when it is used in the FROM clause of another
query. In fact, you can see this at work if you just save one query with a
sort, then run a query of that query with no sort. The output generally looks
the same as doing a single query with no sort. Also, what would the order of
a FROM source mean if the FROM clause is a join of multiple sources? Which
sort would be definitive?

I see where you are going with this and it does make sense. But if the
sort is of no importance then what possible use could First/Last ever
have? Hmm, thought I should see what Help has to say about it and it
does seem to say that the sort is relevant:

"Because records are usually returned in no particular order (unless the
query includes an ORDER BY clause), the records returned by these
functions will be arbitrary."

Min/Max are much easier and predictable to use, so I'm not likely to
switch over. It just seems odd that First/Last exist if they don't
actually do anything meaningful.

--
Bri