LAST agregate function !!!

[Adam Knight]

Set rs2 = cn.execute("select LAST(invoice_date) from invoice where
invoice_registration='0003' and invoice_member_id=" & CInt(rs(0)) & " Order
By invoice_date asc")

You tried to execute a query that does not include the specified expression
'invoice_date' as part of an aggregate function.
/connect/admin/reminder/reminder.asp, line 14

Could someone be kind enough to give me a resolution to the problem above.
How do i accomplish this using JET SQL.

AK

 

[Ken Schaefer]

Why can't you do:

<%
strSQL = _
"SELECT TOP 1 Invoice_Date " & _
"FROM Invoice " & _
"WHERE Invoice_Registation = '0003' " & _
"AND Invoice_Member_ID=" & rs(0) & " " & _
"ORDER BY Invoice_Date DESC"

Set objRSInvoice_Date = objConn.Execute(strSQL)
%>

Cheers
Ken

: Set rs2 = cn.execute("select LAST(invoice_date) from invoice where
: invoice_registration='0003' and invoice_member_id=" & CInt(rs(0)) & "
Order
: By invoice_date asc")
:
: You tried to execute a query that does not include the specified
expression
: 'invoice_date' as part of an aggregate function.
: /connect/admin/reminder/reminder.asp, line 14
:
: Could someone be kind enough to give me a resolution to the problem above.
: How do i accomplish this using JET SQL.
:
: AK
:
:

 

[Adam Knight]

That would be the T-SQL approach, I am actually after the JET SQL approach
!!!

Thanx Anyway !!!

 

[Bob Barrows [MVP]]

Set rs2 = cn.execute("select LAST(invoice_date) from invoice where
invoice_registration='0003' and invoice_member_id=" & CInt(rs(0)) & "
Order By invoice_date asc")

You tried to execute a query that does not include the specified
expression 'invoice_date' as part of an aggregate function.
/connect/admin/reminder/reminder.asp, line 14

Could someone be kind enough to give me a resolution to the problem
above. How do i accomplish this using JET SQL.

You are asking the query engine to do two different things: aggregate and
sort. What is the point of having an Order By clause on a query that will
return a single record?

LAST(invoice_date) will return the invoice_date contained in the last record
of the set of records that meet the requirements in the WHERE clause. This
value will NOT be contained in a field called invoice_date unless you
explicitly tell it to do so using a column alias. With Jet, the name of the
column returned by an expression will be given a default name like Expr001,
which you would see if you tested the query using the Access Query Builder
(you ARE testing the query before attempting to run it from ASP aren't you?)

The Order By clause can refer to a column not mentioned in your SELECT list.
However, when it does, a temporary work table is created that includes the
missing column. Normally, this works fine. In the case where you've used an
aggregate function, however, you cannot have both a column and an aggregate
function in the SELECT list unless the column is contained in a GROUP BY
clause, which your query lacks.

In your case, you need to ask what you are really after: it looks like you
simply want the value of the largest (latest) invoice_date in the set of
records meeting your criteria. This can be done using the MAX function:

SELECT MAX(invoice_date) FROM ....

(no ORDER BY clause needed)


Bob Barrows

 

[Bob Barrows [MVP]]

Actually, that's not a "T-SQL" approach. It is a SQL approach that will work
equally well in both T-SQL and JetSQL. Although it IS a little bit of
"overkill" for this particular problem ...

Bob Barrows

 

[Evertjan.]

Bob Barrows [MVP] wrote on 06 jun 2004 in
microsoft.public.inetserver.asp.general:

LAST(invoice_date) will return the invoice_date contained in the last
record of the set of records that meet the requirements in the WHERE
clause.

I don't get it. In a relational database there should be no last record,
since the data are not stored in succession by definition.

So why does the LAST() function exist?

In your case, you need to ask what you are really after:

Sound advice.

it looks like
you simply want the value of the largest (latest) invoice_date in the
set of records meeting your criteria. This can be done using the MAX
function:

SELECT MAX(invoice_date) FROM ....

(no ORDER BY clause needed)

If you really want the last inserted record [that is not deleted since]
and there is a autoincrement field [named ID].

SELECT MAX(ID) FROM ....

should do to get it's ID.

But to get all that record's fields:

SELECT * FROM .. WHERE ID = MAX(ID)

Or is there a better answer, Bob?

 

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote on 06 jun 2004 in
microsoft.public.inetserver.asp.general:

I don't get it. In a relational database there should be no last
record, since the data are not stored in succession by definition.

So why does the LAST() function exist?

Jet was built for people who may know nothing at all about relational
databases, and who may be under the impression that a first and a last
record exist. Therefore, the First and Last functions were created. I
suspect that under the covers, the query engine is actually doing a Top 1

In your case, you need to ask what you are really after:

Sound advice.

it looks like
you simply want the value of the largest (latest) invoice_date in the
set of records meeting your criteria. This can be done using the MAX
function:

SELECT MAX(invoice_date) FROM ....

(no ORDER BY clause needed)

If you really want the last inserted record [that is not deleted
since] and there is a autoincrement field [named ID].

SELECT MAX(ID) FROM ....

should do to get it's ID.

But to get all that record's fields:

SELECT * FROM .. WHERE ID = MAX(ID)

Or is there a better answer, Bob?

You would actually need a subquery for this:

Select * FROM ...
WHERE ID =
(SELECT MAX(ID) FROM ...)


Bob Barrows

 

[Ken Schaefer]

Hi Adam,

There is nothing T-SQL about this (by T-SQL I assume you mean SQL Server's
Transact SQL). What I posted is ANSI SQL (the SQL standard), which works
just as well in Access (Jet) as well as SQL Server (and probably most other
databases as well).

Cheers
Ken


: That would be the T-SQL approach, I am actually after the JET SQL approach
: !!!
:
: Thanx Anyway !!!
:
:
: : > Why can't you do:
: >
: > <%
: > strSQL = _
: > "SELECT TOP 1 Invoice_Date " & _
: > "FROM Invoice " & _
: > "WHERE Invoice_Registation = '0003' " & _
: > "AND Invoice_Member_ID=" & rs(0) & " " & _
: > "ORDER BY Invoice_Date DESC"
: >
: > Set objRSInvoice_Date = objConn.Execute(strSQL)
: > %>
: >
: > Cheers
: > Ken
: >
: > : > : Set rs2 = cn.execute("select LAST(invoice_date) from invoice where
: > : invoice_registration='0003' and invoice_member_id=" & CInt(rs(0)) & "
: > Order
: > : By invoice_date asc")
: > :
: > : You tried to execute a query that does not include the specified
: > expression
: > : 'invoice_date' as part of an aggregate function.
: > : /connect/admin/reminder/reminder.asp, line 14
: > :
: > : Could someone be kind enough to give me a resolution to the problem
: above.
: > : How do i accomplish this using JET SQL.
: > :
: > : AK
: > :
: > :
: >
: >
:
: