Help Please with First () and Last ()

  • Thread starter Thread starter mikeS
  • Start date Start date
M

mikeS

I am not getting the first and last values of a field in
my query. I am pretty sure that I am using the function
correctly. Here is my sql statement:
SELECT [Equip #], FIRST([Last Meter]) AS FirstReading,
LAST([Last Meter]) AS LastReading
FROM Monthly_Update_Rental
WHERE [Equip #] = "R9N0769"
GROUP BY [Equip #];

It gives me a last and first, but they are not the last
and the first.
 
I want the first and last physical records in the record
set, not the min and max. The values unfortunately are
not always ascending. My sql statemennt is not giving me
the physical first and last record. I know that I have
used it before for similar purposes, but can not
understand why it is not returning what I want now...

-----Original Message-----
Mike-

"First" and "Last" return the first and last *physical* records found in the
recordset. When you're working in a database that has, over time, had lots
of inserts and deletes, the first and last records for a group are
essentially random values. Perhaps what you want in this case is
Min(LastMeter) and Max(LastMeter) -- assuming the meter readings are always
ascending.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
mikeS said:
I am not getting the first and last values of a field in
my query. I am pretty sure that I am using the function
correctly. Here is my sql statement:
SELECT [Equip #], FIRST([Last Meter]) AS FirstReading,
LAST([Last Meter]) AS LastReading
FROM Monthly_Update_Rental
WHERE [Equip #] = "R9N0769"
GROUP BY [Equip #];

It gives me a last and first, but they are not the last
and the first.
Click to expand...


.
Click to expand...
 
Hi,


If you have a date_time stamping field, take a look at
http://www.mvps.org/access/queries/qry0020.htm


Hoping it may help,
Vanderghast, Access MVP


mikeS said:
I want the first and last physical records in the record
set, not the min and max. The values unfortunately are
not always ascending. My sql statemennt is not giving me
the physical first and last record. I know that I have
used it before for similar purposes, but can not
understand why it is not returning what I want now...

-----Original Message-----
Mike-

"First" and "Last" return the first and last *physical* records found in the
recordset. When you're working in a database that has, over time, had lots
of inserts and deletes, the first and last records for a group are
essentially random values. Perhaps what you want in this case is
Min(LastMeter) and Max(LastMeter) -- assuming the meter readings are always
ascending.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
mikeS said:
I am not getting the first and last values of a field in
my query. I am pretty sure that I am using the function
correctly. Here is my sql statement:
SELECT [Equip #], FIRST([Last Meter]) AS FirstReading,
LAST([Last Meter]) AS LastReading
FROM Monthly_Update_Rental
WHERE [Equip #] = "R9N0769"
GROUP BY [Equip #];

It gives me a last and first, but they are not the last
and the first.
Click to expand...


.
Click to expand...
Click to expand...
 
See also Vanderghast's reply.

You probably added/deleted records and then compacted the database.

What defines "firstness" and "lastness" in the table? I would have thought
that the lowest meter reading for a piece of equipment would be the first
one and the highest the last. As Vanderghast asked, is there some other
field in the table that determines sequence?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
mikeS said:
I want the first and last physical records in the record
set, not the min and max. The values unfortunately are
not always ascending. My sql statemennt is not giving me
the physical first and last record. I know that I have
used it before for similar purposes, but can not
understand why it is not returning what I want now...

-----Original Message-----
Mike-

"First" and "Last" return the first and last *physical* records found in the
recordset. When you're working in a database that has, over time, had lots
of inserts and deletes, the first and last records for a group are
essentially random values. Perhaps what you want in this case is
Min(LastMeter) and Max(LastMeter) -- assuming the meter readings are always
ascending.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
mikeS said:
I am not getting the first and last values of a field in
my query. I am pretty sure that I am using the function
correctly. Here is my sql statement:
SELECT [Equip #], FIRST([Last Meter]) AS FirstReading,
LAST([Last Meter]) AS LastReading
FROM Monthly_Update_Rental
WHERE [Equip #] = "R9N0769"
GROUP BY [Equip #];

It gives me a last and first, but they are not the last
and the first.
Click to expand...


.
Click to expand...
Click to expand...
 
I bet you're missing the magical TOP 100 PERCENT subquery
that is required in order for First and Last to work
predictably.

The first thing you must do is set a query that determines
the sort order.

For example:

SELECT TOP 100 PERCENT * From tblMeterReading ORDER BY
[Equip #], [Last Meter]

--- Now use this query as the base query for your first()
and last() query.

The bottom line is *if you don't specify Top* the Order By
is ignored when it's used as a subquery.

David Atkins, MCP
 
David-

If your ORDER BY solution works, then Min and Max should also work -- but
the OP says this isn't so. First and Last are just a kludge to get a random
value from the group when you don't want to use one of the other aggregate
functions. They should never be used, IMO.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi John,


I disagree on the use of First and Last. As example, try to do the same,
in MS SQL Server:


Table1 ' table name
f1, f2, f3 ' fields name
1 99 1
1 1 99
1 50 50


SQL:

SELECT f1, FIRST(f2), FIRST(f3) FROM Table1 GROUP BY f1 ;


In Jet, the result would be ANY of the three record. By opposition,
for illustration, if you use MIN or MAX, you would get something like:


1 99 99


but f2+f3 <> 100 illustrates the fact they do not come, both (f2
and f3), from the same existing record. So, what would it be in MS SQL
Server (who does not have FIRST, neither LAST) ? (and I am a very bad guy
in this case, I don't supply a primary key... )
:-)


So, there are cases where FIRST and LAST are important. A more concrete
example is to "remove" duplicated values. If field f1 define the "group"
(say, the clientID) while f2 is the phone number and f3 the address, we
want to keep and we want ONE record per clientID, and but we don't want to
mix the phone number in Texas with an address in Washington. The Jet
statement is exactly like the simple one I typed... but without
FIRST/LAST... ?


I agree on the "poor" choice of word, they have the tendency to be read
as "earliest" and "latest", while they are not.

Isn't there an equivalent in Oracle (for "get from the same row") ?



Vanderghast, Access MVP



John Viescas said:
David-

If your ORDER BY solution works, then Min and Max should also work -- but
the OP says this isn't so. First and Last are just a kludge to get a random
value from the group when you don't want to use one of the other aggregate
functions. They should never be used, IMO.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
GreySky said:
I bet you're missing the magical TOP 100 PERCENT subquery
that is required in order for First and Last to work
predictably.

The first thing you must do is set a query that determines
the sort order.

For example:

SELECT TOP 100 PERCENT * From tblMeterReading ORDER BY
[Equip #], [Last Meter]

--- Now use this query as the base query for your first()
and last() query.

The bottom line is *if you don't specify Top* the Order By
is ignored when it's used as a subquery.

David Atkins, MCP
Click to expand...
Click to expand...
 
Michel-

Your example says "use MIN or Max, you would get something like:

1 99 99 "

You'll get that answer only if you use MAX for both.

SELECT f1, MIN(f2), MIN(f3) FROM Table1 GROUP BY f1 ;

Yields:

1 1 1


SELECT f1, MIN(f2), MAX(f3) FROM Table1 GROUP BY f1 ;

Yields:

1 1 99

David's example suggested sorting solves the problem, but why bother when
it's one column you're looking at? Using his sort solution,

SELECT f1, FIRST(f2), LAST(f2)
FROM (SELECT TOP 100 PERCENT f1, f2
FROM Table 1 ORDER BY f1, f2) AS T
GROUP BY f1

... yields the same result as:

SELECT F1, MIN(f2), MAX(f2)
FROM Table1
GROUP BY f1

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Michel Walsh said:
Hi John,


I disagree on the use of First and Last. As example, try to do the same,
in MS SQL Server:


Table1 ' table name
f1, f2, f3 ' fields name
1 99 1
1 1 99
1 50 50


SQL:

SELECT f1, FIRST(f2), FIRST(f3) FROM Table1 GROUP BY f1 ;


In Jet, the result would be ANY of the three record. By opposition,
for illustration, if you use MIN or MAX, you would get something like:


1 99 99


but f2+f3 <> 100 illustrates the fact they do not come, both (f2
and f3), from the same existing record. So, what would it be in MS SQL
Server (who does not have FIRST, neither LAST) ? (and I am a very bad guy
in this case, I don't supply a primary key... )
:-)


So, there are cases where FIRST and LAST are important. A more concrete
example is to "remove" duplicated values. If field f1 define the "group"
(say, the clientID) while f2 is the phone number and f3 the address, we
want to keep and we want ONE record per clientID, and but we don't want to
mix the phone number in Texas with an address in Washington. The Jet
statement is exactly like the simple one I typed... but without
FIRST/LAST... ?


I agree on the "poor" choice of word, they have the tendency to be read
as "earliest" and "latest", while they are not.

Isn't there an equivalent in Oracle (for "get from the same row") ?



Vanderghast, Access MVP



John Viescas said:
David-

If your ORDER BY solution works, then Min and Max should also work -- but
the OP says this isn't so. First and Last are just a kludge to get a random
value from the group when you don't want to use one of the other aggregate
functions. They should never be used, IMO.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
GreySky said:
I bet you're missing the magical TOP 100 PERCENT subquery
that is required in order for First and Last to work
predictably.

The first thing you must do is set a query that determines
the sort order.

For example:

SELECT TOP 100 PERCENT * From tblMeterReading ORDER BY
[Equip #], [Last Meter]

--- Now use this query as the base query for your first()
and last() query.

The bottom line is *if you don't specify Top* the Order By
is ignored when it's used as a subquery.

David Atkins, MCP
Click to expand...
Click to expand...
Click to expand...
 
Back
Top