Get all and the last ID of database

[midimidi]

Newbie here. I know very little about queries. How can I find the last ID of
a recordset? The only solution I found was the following, and while it works,
I can't seem to figure out how to have both it and a 'get all' in the same
query. SO - in the end, I currently have the following TWO queries, and I'm
assuming there's a better way to do this since they're both coming from the
same database. I need the last ID for an indexed cfloop. The database is
currently MS Access. <cfquery name='all' datasource='month'> SELECT * FROM
month </cfquery> <cfquery name='all_count' datasource='month'> SELECT
Count(*) as LastID FROM month </cfquery>

[Bill Sahlas]

to get the last value in any column you'd use the LAST() scalar function -
this is a common function among database verndors. These are some other
methods - LAST(<FIELDNAME>), MAX(<FIELDNAME>), MIN(<FIELDNAME>),
FIRST(<FIELDNAME>), AVG(<FIELDNAME>).

For you're example though it appears that you're using the Count(*) method
to get the number for your to="" part of the cfloop. You can use this
instead to="all.recordcount"

Here are some other attributes at your disposal

Variable name Description
query_name.currentRow
Current row of query that cfoutput is processing.

query_name.columnList
Comma-delimited list of the query columns.

query_name.RecordCount
Number of records (rows) returned from the query.



Also see
[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=p art_cfm.htm[/url]


"midimidi" <webforumsuser@macromedia.com> wrote in message
news:cvd80i$kkr$1@forums.macromedia.com...

> Newbie here. I know very little about queries. How can I find the last
> ID of
> a recordset? The only solution I found was the following, and while it
> works,
> I can't seem to figure out how to have both it and a 'get all' in the same
> query. SO - in the end, I currently have the following TWO queries, and
> I'm
> assuming there's a better way to do this since they're both coming from
> the
> same database. I need the last ID for an indexed cfloop. The database is
> currently MS Access. <cfquery name='all' datasource='month'> SELECT *
> FROM
> month </cfquery> <cfquery name='all_count' datasource='month'> SELECT
> Count(*) as LastID FROM month </cfquery>
>

[Steve-DDOTS]

You can also query
SELECT Max(RecordID) AS ThisMaxRecoredID FROM xxxxxx

That will produce the last recordid as thismaxrecordid

[Steve-DDOTS]

Of course, 'RecordID' is your key incrementing ID for the table.

[kyle969]

You can treat the resultSet as an array. To get the last value try this:

lastVal = queryName.columnName[queryName.recordCount];