Please help with aggregate functions!

  • Thread starter Thread starter Dave Taylor
  • Start date Start date
D

Dave Taylor

I have a database that records elemental analysis numbers from an X-ray
analyzer about once every 60 to 90 seconds, each record includes a record ID
(sequential), timestamp, name of the stream being sampled, and the assay
values. I wrote a query that sorted the table by stream name and timestamp
and then grouped by the stream name and pulled the last() values out for
each stream name. This seemed to work until yesterday, and now the results
are all from January 19th and I *know* there is later data (i.e. I can see
data in the main historian table from just a few minutes ago). I've
attached my SQL query text below and for the life of me cannot figure out
why in the heck it wont pull the latest values. Any help is greatly
appreciated. Is there a better way to achieve this same result?

Also, a bothersome quirk...when I save this query in Access, it always
converts the parenthesis to square brackets on the inner SELECT statement
and then complains about invalid bracketing...yet if I convert it back to
parenthesis, it works just fine (less the problem of not picking up the
LAST() record of the group). To get around this, I did try converting the
inner SELECT to a named query, and that got rid of this issue, however it
still didnt pickup the LAST() record of the group.

Thanks

Dave Taylor


SELECT Historian.stream_name, Last(Historian.timestamp) AS [timestamp],
Last(Historian.fe) AS fe, Last(Historian.pb) AS pb, Last(Historian.zn) AS
zn, Last(Historian.solids) AS solids, Last(Historian.result) AS result,
"PXDetail.aspx?rec_id=" & Format(Last(Historian.rec_id)) AS url
FROM (SELECT * FROM Historian ORDER BY [stream_name], [timestamp]) AS
Historian
GROUP BY Historian.stream_name;
 
Dear Dave:

From the Help in Access XP:

The First and Last functions are analogous to the MoveFirst and
MoveLast methods of a DAO Recordset object. They simply return the
value of a specified field in the first or last record, respectively,
of the result set returned by a query. 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.

end quote

As you have provided no sorting for the rows, just which row is
returned by First or Last is "arbitrary".

I suggest you want the MAX() function instead of the LAST() function.
You should use the Last function only in queries where you want
unpredictable results.

In short, the word "Last" may sound like what you want, but it isn't.
Last has nothing to do with chronology.

Now, if you want the other information (fe, pb. zn, solids, result,
and url) from the same row as that maximum date, we have some more
work to do! Changing these to MAX() will determine the maximum value
of each of these things irrespective of what row they are in, rather
than finding the ones in the MAX(timestamp) row.

How does this do?

SELECT stream_name, timestamp, fe, pb, zn, solids, result, url
FROM Historian T
WHERE timestamp = (SELECT MAX(timestamp) FROM Historian T1
WHERE T1.stream_name = T.stream_name);

Unlike the Last function which selects only one row, though it does so
arbitrarily, this query could return more than one row for each
stream_name if there are two rows with the same timestamp and they are
both the most recent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a database that records elemental analysis numbers from an X-ray
analyzer about once every 60 to 90 seconds, each record includes a record ID
(sequential), timestamp, name of the stream being sampled, and the assay
values. I wrote a query that sorted the table by stream name and timestamp
and then grouped by the stream name and pulled the last() values out for
each stream name. This seemed to work until yesterday, and now the results
are all from January 19th and I *know* there is later data (i.e. I can see
data in the main historian table from just a few minutes ago). I've
attached my SQL query text below and for the life of me cannot figure out
why in the heck it wont pull the latest values. Any help is greatly
appreciated. Is there a better way to achieve this same result?

Also, a bothersome quirk...when I save this query in Access, it always
converts the parenthesis to square brackets on the inner SELECT statement
and then complains about invalid bracketing...yet if I convert it back to
parenthesis, it works just fine (less the problem of not picking up the
LAST() record of the group). To get around this, I did try converting the
inner SELECT to a named query, and that got rid of this issue, however it
still didnt pickup the LAST() record of the group.

Thanks

Dave Taylor


SELECT Historian.stream_name, Last(Historian.timestamp) AS [timestamp],
Last(Historian.fe) AS fe, Last(Historian.pb) AS pb, Last(Historian.zn) AS
zn, Last(Historian.solids) AS solids, Last(Historian.result) AS result,
"PXDetail.aspx?rec_id=" & Format(Last(Historian.rec_id)) AS url
FROM (SELECT * FROM Historian ORDER BY [stream_name], [timestamp]) AS
Historian
GROUP BY Historian.stream_name;
Click to expand...
 
Tom,

Thank you for the reply! I had read the Access help about the returned
record from Last() being arbitrary, but thought since I was ordering the
records via the inner SELECT statement, they would come back in that order
and moving last within the group would work. Obviously not. :-)

I tried your SELECT method and it returns exactly the right data, the only
problem is the query is very slow to execute. The Historian table contains
about 5000 records (approx because old records are removed once/hour) and on
my machine it took about 38 seconds to run (dual 2.4GHz P4, 1GB RAM). On
the production machine (I think it's maybe a 1.7GHz P4 with 512MB), the
query is run once/minute where the returned data is transmitted serially to
another computer so timing could become a problem. It just doesnt seem like
there is that much data that it should take so long. Oh well...continuing
to work on this problem!

Thanks again

Dave Taylor




Tom Ellison said:
Dear Dave:

From the Help in Access XP:

The First and Last functions are analogous to the MoveFirst and
MoveLast methods of a DAO Recordset object. They simply return the
value of a specified field in the first or last record, respectively,
of the result set returned by a query. 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.

end quote

As you have provided no sorting for the rows, just which row is
returned by First or Last is "arbitrary".

I suggest you want the MAX() function instead of the LAST() function.
You should use the Last function only in queries where you want
unpredictable results.

In short, the word "Last" may sound like what you want, but it isn't.
Last has nothing to do with chronology.

Now, if you want the other information (fe, pb. zn, solids, result,
and url) from the same row as that maximum date, we have some more
work to do! Changing these to MAX() will determine the maximum value
of each of these things irrespective of what row they are in, rather
than finding the ones in the MAX(timestamp) row.

How does this do?

SELECT stream_name, timestamp, fe, pb, zn, solids, result, url
FROM Historian T
WHERE timestamp = (SELECT MAX(timestamp) FROM Historian T1
WHERE T1.stream_name = T.stream_name);

Unlike the Last function which selects only one row, though it does so
arbitrarily, this query could return more than one row for each
stream_name if there are two rows with the same timestamp and they are
both the most recent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a database that records elemental analysis numbers from an X-ray
analyzer about once every 60 to 90 seconds, each record includes a record ID
(sequential), timestamp, name of the stream being sampled, and the assay
values. I wrote a query that sorted the table by stream name and timestamp
and then grouped by the stream name and pulled the last() values out for
each stream name. This seemed to work until yesterday, and now the results
are all from January 19th and I *know* there is later data (i.e. I can see
data in the main historian table from just a few minutes ago). I've
attached my SQL query text below and for the life of me cannot figure out
why in the heck it wont pull the latest values. Any help is greatly
appreciated. Is there a better way to achieve this same result?

Also, a bothersome quirk...when I save this query in Access, it always
converts the parenthesis to square brackets on the inner SELECT statement
and then complains about invalid bracketing...yet if I convert it back to
parenthesis, it works just fine (less the problem of not picking up the
LAST() record of the group). To get around this, I did try converting the
inner SELECT to a named query, and that got rid of this issue, however it
still didnt pickup the LAST() record of the group.

Thanks

Dave Taylor


SELECT Historian.stream_name, Last(Historian.timestamp) AS [timestamp],
Last(Historian.fe) AS fe, Last(Historian.pb) AS pb, Last(Historian.zn) AS
zn, Last(Historian.solids) AS solids, Last(Historian.result) AS result,
"PXDetail.aspx?rec_id=" & Format(Last(Historian.rec_id)) AS url
FROM (SELECT * FROM Historian ORDER BY [stream_name], [timestamp]) AS
Historian
GROUP BY Historian.stream_name;
Click to expand...
Click to expand...
 
Dear Dave:

I could see what you were using to try to get it to work, and I could
also see that it seemed to make sense given the help documentation
available. But this isn't a way I've ever heard of using that
actually works.

As to performance, do you have an index on the Historian table for the
composite key stream_name and timestamp (in that order)? I think this
is the thing the query could use to speed up its job.

For real-time processing, especially if you continue to have
performance problems, I would recommend you consider MSDE instead of
Jet for the engine. Making such a switch could be a fair piece of
work, especially if you also must learn to work in MSDE. In the long
run, this switch has been invaluable to me.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Thank you for the reply! I had read the Access help about the returned
record from Last() being arbitrary, but thought since I was ordering the
records via the inner SELECT statement, they would come back in that order
and moving last within the group would work. Obviously not. :-)

I tried your SELECT method and it returns exactly the right data, the only
problem is the query is very slow to execute. The Historian table contains
about 5000 records (approx because old records are removed once/hour) and on
my machine it took about 38 seconds to run (dual 2.4GHz P4, 1GB RAM). On
the production machine (I think it's maybe a 1.7GHz P4 with 512MB), the
query is run once/minute where the returned data is transmitted serially to
another computer so timing could become a problem. It just doesnt seem like
there is that much data that it should take so long. Oh well...continuing
to work on this problem!

Thanks again

Dave Taylor




Tom Ellison said:
Dear Dave:

From the Help in Access XP:

The First and Last functions are analogous to the MoveFirst and
MoveLast methods of a DAO Recordset object. They simply return the
value of a specified field in the first or last record, respectively,
of the result set returned by a query. 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.

end quote

As you have provided no sorting for the rows, just which row is
returned by First or Last is "arbitrary".

I suggest you want the MAX() function instead of the LAST() function.
You should use the Last function only in queries where you want
unpredictable results.

In short, the word "Last" may sound like what you want, but it isn't.
Last has nothing to do with chronology.

Now, if you want the other information (fe, pb. zn, solids, result,
and url) from the same row as that maximum date, we have some more
work to do! Changing these to MAX() will determine the maximum value
of each of these things irrespective of what row they are in, rather
than finding the ones in the MAX(timestamp) row.

How does this do?

SELECT stream_name, timestamp, fe, pb, zn, solids, result, url
FROM Historian T
WHERE timestamp = (SELECT MAX(timestamp) FROM Historian T1
WHERE T1.stream_name = T.stream_name);

Unlike the Last function which selects only one row, though it does so
arbitrarily, this query could return more than one row for each
stream_name if there are two rows with the same timestamp and they are
both the most recent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a database that records elemental analysis numbers from an X-ray
analyzer about once every 60 to 90 seconds, each record includes a record ID
(sequential), timestamp, name of the stream being sampled, and the assay
values. I wrote a query that sorted the table by stream name and timestamp
and then grouped by the stream name and pulled the last() values out for
each stream name. This seemed to work until yesterday, and now the results
are all from January 19th and I *know* there is later data (i.e. I can see
data in the main historian table from just a few minutes ago). I've
attached my SQL query text below and for the life of me cannot figure out
why in the heck it wont pull the latest values. Any help is greatly
appreciated. Is there a better way to achieve this same result?

Also, a bothersome quirk...when I save this query in Access, it always
converts the parenthesis to square brackets on the inner SELECT statement
and then complains about invalid bracketing...yet if I convert it back to
parenthesis, it works just fine (less the problem of not picking up the
LAST() record of the group). To get around this, I did try converting the
inner SELECT to a named query, and that got rid of this issue, however it
still didnt pickup the LAST() record of the group.

Thanks

Dave Taylor


SELECT Historian.stream_name, Last(Historian.timestamp) AS [timestamp],
Last(Historian.fe) AS fe, Last(Historian.pb) AS pb, Last(Historian.zn) AS
zn, Last(Historian.solids) AS solids, Last(Historian.result) AS result,
"PXDetail.aspx?rec_id=" & Format(Last(Historian.rec_id)) AS url
FROM (SELECT * FROM Historian ORDER BY [stream_name], [timestamp]) AS
Historian
GROUP BY Historian.stream_name;
Click to expand...
Click to expand...
Click to expand...
 
Solved it.

By first creating a query that returns only the max values for the rec_id
field (though certianly could have used the timestamp field as well) and
then creating another query that joins the first query to the historian
table based on the rec_id's. This returns the latest data and is nearly
instantaneous.

The first query is "Most Recent ID"
SELECT Historian.stream_name, Max(Historian.rec_id) AS MaxOfrec_id
FROM Historian
GROUP BY Historian.stream_name
ORDER BY Historian.stream_name;

The second query is "G2 Report Query"
SELECT [Most Recent ID].stream_name, Historian.timestamp, Historian.fe,
Historian.pb, Historian.zn, Historian.solids, Historian.result,
"PXDetail.aspx?rec_id=" & Format([rec_id]) AS url
FROM [Most Recent ID] INNER JOIN Historian ON [Most Recent ID].MaxOfrec_id =
Historian.rec_id;

Thanks again

Dave


Tom Ellison said:
Dear Dave:

From the Help in Access XP:

The First and Last functions are analogous to the MoveFirst and
MoveLast methods of a DAO Recordset object. They simply return the
value of a specified field in the first or last record, respectively,
of the result set returned by a query. 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.

end quote

As you have provided no sorting for the rows, just which row is
returned by First or Last is "arbitrary".

I suggest you want the MAX() function instead of the LAST() function.
You should use the Last function only in queries where you want
unpredictable results.

In short, the word "Last" may sound like what you want, but it isn't.
Last has nothing to do with chronology.

Now, if you want the other information (fe, pb. zn, solids, result,
and url) from the same row as that maximum date, we have some more
work to do! Changing these to MAX() will determine the maximum value
of each of these things irrespective of what row they are in, rather
than finding the ones in the MAX(timestamp) row.

How does this do?

SELECT stream_name, timestamp, fe, pb, zn, solids, result, url
FROM Historian T
WHERE timestamp = (SELECT MAX(timestamp) FROM Historian T1
WHERE T1.stream_name = T.stream_name);

Unlike the Last function which selects only one row, though it does so
arbitrarily, this query could return more than one row for each
stream_name if there are two rows with the same timestamp and they are
both the most recent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a database that records elemental analysis numbers from an X-ray
analyzer about once every 60 to 90 seconds, each record includes a record ID
(sequential), timestamp, name of the stream being sampled, and the assay
values. I wrote a query that sorted the table by stream name and timestamp
and then grouped by the stream name and pulled the last() values out for
each stream name. This seemed to work until yesterday, and now the results
are all from January 19th and I *know* there is later data (i.e. I can see
data in the main historian table from just a few minutes ago). I've
attached my SQL query text below and for the life of me cannot figure out
why in the heck it wont pull the latest values. Any help is greatly
appreciated. Is there a better way to achieve this same result?

Also, a bothersome quirk...when I save this query in Access, it always
converts the parenthesis to square brackets on the inner SELECT statement
and then complains about invalid bracketing...yet if I convert it back to
parenthesis, it works just fine (less the problem of not picking up the
LAST() record of the group). To get around this, I did try converting the
inner SELECT to a named query, and that got rid of this issue, however it
still didnt pickup the LAST() record of the group.

Thanks

Dave Taylor


SELECT Historian.stream_name, Last(Historian.timestamp) AS [timestamp],
Last(Historian.fe) AS fe, Last(Historian.pb) AS pb, Last(Historian.zn) AS
zn, Last(Historian.solids) AS solids, Last(Historian.result) AS result,
"PXDetail.aspx?rec_id=" & Format(Last(Historian.rec_id)) AS url
FROM (SELECT * FROM Historian ORDER BY [stream_name], [timestamp]) AS
Historian
GROUP BY Historian.stream_name;
Click to expand...
Click to expand...
 
Dear Dave:

Good job! This is a viable alternative, but I was trying to give you
a simpler solution by first creating an index. I was saving this one
if you got no satisfaction from the indexing.

It is a shame that Jet's queries do not optimize for you. With an
imporved plan, it would have done what your query is now doing, but
without you having to change it to get the performance improvement.

MSDE would almost certainly have done this for you, and using Query
Analyzer you could learn easily what indexes would help. But you
should be alright for a while with what you have now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Solved it.

By first creating a query that returns only the max values for the rec_id
field (though certianly could have used the timestamp field as well) and
then creating another query that joins the first query to the historian
table based on the rec_id's. This returns the latest data and is nearly
instantaneous.

The first query is "Most Recent ID"
SELECT Historian.stream_name, Max(Historian.rec_id) AS MaxOfrec_id
FROM Historian
GROUP BY Historian.stream_name
ORDER BY Historian.stream_name;

The second query is "G2 Report Query"
SELECT [Most Recent ID].stream_name, Historian.timestamp, Historian.fe,
Historian.pb, Historian.zn, Historian.solids, Historian.result,
"PXDetail.aspx?rec_id=" & Format([rec_id]) AS url
FROM [Most Recent ID] INNER JOIN Historian ON [Most Recent ID].MaxOfrec_id =
Historian.rec_id;

Thanks again

Dave


Tom Ellison said:
Dear Dave:

From the Help in Access XP:

The First and Last functions are analogous to the MoveFirst and
MoveLast methods of a DAO Recordset object. They simply return the
value of a specified field in the first or last record, respectively,
of the result set returned by a query. 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.

end quote

As you have provided no sorting for the rows, just which row is
returned by First or Last is "arbitrary".

I suggest you want the MAX() function instead of the LAST() function.
You should use the Last function only in queries where you want
unpredictable results.

In short, the word "Last" may sound like what you want, but it isn't.
Last has nothing to do with chronology.

Now, if you want the other information (fe, pb. zn, solids, result,
and url) from the same row as that maximum date, we have some more
work to do! Changing these to MAX() will determine the maximum value
of each of these things irrespective of what row they are in, rather
than finding the ones in the MAX(timestamp) row.

How does this do?

SELECT stream_name, timestamp, fe, pb, zn, solids, result, url
FROM Historian T
WHERE timestamp = (SELECT MAX(timestamp) FROM Historian T1
WHERE T1.stream_name = T.stream_name);

Unlike the Last function which selects only one row, though it does so
arbitrarily, this query could return more than one row for each
stream_name if there are two rows with the same timestamp and they are
both the most recent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a database that records elemental analysis numbers from an X-ray
analyzer about once every 60 to 90 seconds, each record includes a record ID
(sequential), timestamp, name of the stream being sampled, and the assay
values. I wrote a query that sorted the table by stream name and timestamp
and then grouped by the stream name and pulled the last() values out for
each stream name. This seemed to work until yesterday, and now the results
are all from January 19th and I *know* there is later data (i.e. I can see
data in the main historian table from just a few minutes ago). I've
attached my SQL query text below and for the life of me cannot figure out
why in the heck it wont pull the latest values. Any help is greatly
appreciated. Is there a better way to achieve this same result?

Also, a bothersome quirk...when I save this query in Access, it always
converts the parenthesis to square brackets on the inner SELECT statement
and then complains about invalid bracketing...yet if I convert it back to
parenthesis, it works just fine (less the problem of not picking up the
LAST() record of the group). To get around this, I did try converting the
inner SELECT to a named query, and that got rid of this issue, however it
still didnt pickup the LAST() record of the group.

Thanks

Dave Taylor


SELECT Historian.stream_name, Last(Historian.timestamp) AS [timestamp],
Last(Historian.fe) AS fe, Last(Historian.pb) AS pb, Last(Historian.zn) AS
zn, Last(Historian.solids) AS solids, Last(Historian.result) AS result,
"PXDetail.aspx?rec_id=" & Format(Last(Historian.rec_id)) AS url
FROM (SELECT * FROM Historian ORDER BY [stream_name], [timestamp]) AS
Historian
GROUP BY Historian.stream_name;
Click to expand...
Click to expand...
Click to expand...
 
Tom,

Thanks again for the help. I originally considered using MSDE in our X-ray
analyzer, but opted for Access instead since the PC used to control the
analyzer is already loaded with tasks (the control software is a Windows
service written in VB.NET - so its a memory hog just by virtue of being a
..NET app, add to that the web interface to the service and IIS...we're
starting to demand a lot from the PC...so the thought of running SQL
Server/MSDE seemed like too much). I may return to the MSDE route though
and just beef up the computer inside the analyzer (currently its a 2GHz
machine with only 256MB RAM)...but that'll be version 2.0 :-)

-- Dave


Tom Ellison said:
Dear Dave:

Good job! This is a viable alternative, but I was trying to give you
a simpler solution by first creating an index. I was saving this one
if you got no satisfaction from the indexing.

It is a shame that Jet's queries do not optimize for you. With an
imporved plan, it would have done what your query is now doing, but
without you having to change it to get the performance improvement.

MSDE would almost certainly have done this for you, and using Query
Analyzer you could learn easily what indexes would help. But you
should be alright for a while with what you have now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Solved it.

By first creating a query that returns only the max values for the rec_id
field (though certianly could have used the timestamp field as well) and
then creating another query that joins the first query to the historian
table based on the rec_id's. This returns the latest data and is nearly
instantaneous.

The first query is "Most Recent ID"
SELECT Historian.stream_name, Max(Historian.rec_id) AS MaxOfrec_id
FROM Historian
GROUP BY Historian.stream_name
ORDER BY Historian.stream_name;

The second query is "G2 Report Query"
SELECT [Most Recent ID].stream_name, Historian.timestamp, Historian.fe,
Historian.pb, Historian.zn, Historian.solids, Historian.result,
"PXDetail.aspx?rec_id=" & Format([rec_id]) AS url
FROM [Most Recent ID] INNER JOIN Historian ON [Most Recent ID].MaxOfrec_id =
Historian.rec_id;

Thanks again

Dave


Tom Ellison said:
Dear Dave:

From the Help in Access XP:

The First and Last functions are analogous to the MoveFirst and
MoveLast methods of a DAO Recordset object. They simply return the
value of a specified field in the first or last record, respectively,
of the result set returned by a query. 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.

end quote

As you have provided no sorting for the rows, just which row is
returned by First or Last is "arbitrary".

I suggest you want the MAX() function instead of the LAST() function.
You should use the Last function only in queries where you want
unpredictable results.

In short, the word "Last" may sound like what you want, but it isn't.
Last has nothing to do with chronology.

Now, if you want the other information (fe, pb. zn, solids, result,
and url) from the same row as that maximum date, we have some more
work to do! Changing these to MAX() will determine the maximum value
of each of these things irrespective of what row they are in, rather
than finding the ones in the MAX(timestamp) row.

How does this do?

SELECT stream_name, timestamp, fe, pb, zn, solids, result, url
FROM Historian T
WHERE timestamp = (SELECT MAX(timestamp) FROM Historian T1
WHERE T1.stream_name = T.stream_name);

Unlike the Last function which selects only one row, though it does so
arbitrarily, this query could return more than one row for each
stream_name if there are two rows with the same timestamp and they are
both the most recent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jan 2004 16:27:26 -0700, "Dave Taylor"

I have a database that records elemental analysis numbers from an X-ray
analyzer about once every 60 to 90 seconds, each record includes a
Click to expand...
record
ID
(sequential), timestamp, name of the stream being sampled, and the assay
values. I wrote a query that sorted the table by stream name and timestamp
and then grouped by the stream name and pulled the last() values out for
each stream name. This seemed to work until yesterday, and now the results
are all from January 19th and I *know* there is later data (i.e. I can see
data in the main historian table from just a few minutes ago). I've
attached my SQL query text below and for the life of me cannot figure out
why in the heck it wont pull the latest values. Any help is greatly
appreciated. Is there a better way to achieve this same result?

Also, a bothersome quirk...when I save this query in Access, it always
converts the parenthesis to square brackets on the inner SELECT statement
and then complains about invalid bracketing...yet if I convert it back to
parenthesis, it works just fine (less the problem of not picking up the
LAST() record of the group). To get around this, I did try converting the
inner SELECT to a named query, and that got rid of this issue, however it
still didnt pickup the LAST() record of the group.

Thanks

Dave Taylor


SELECT Historian.stream_name, Last(Historian.timestamp) AS [timestamp],
Last(Historian.fe) AS fe, Last(Historian.pb) AS pb, Last(Historian.zn) AS
zn, Last(Historian.solids) AS solids, Last(Historian.result) AS result,
"PXDetail.aspx?rec_id=" & Format(Last(Historian.rec_id)) AS url
FROM (SELECT * FROM Historian ORDER BY [stream_name], [timestamp]) AS
Historian
GROUP BY Historian.stream_name;
Click to expand...
Click to expand...
Click to expand...
 
Dear Dave:

I would say that, with its better optimization, SQL Server or MSDE
will get more out of a PC than Jet will. You might want to bump the
memory up a bit. Also, MSDE will run on a workstation, it does not
require a server OS. You could put this on another computer within
the LAN. It wouldn't have to be on the "analyzer" computer, and would
probably be best not to do so. However, whatever computer you put it
on, it has to be turned on for anyone to access it. That's obvious,
but sometimes overlooked.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Thanks again for the help. I originally considered using MSDE in our X-ray
analyzer, but opted for Access instead since the PC used to control the
analyzer is already loaded with tasks (the control software is a Windows
service written in VB.NET - so its a memory hog just by virtue of being a
.NET app, add to that the web interface to the service and IIS...we're
starting to demand a lot from the PC...so the thought of running SQL
Server/MSDE seemed like too much). I may return to the MSDE route though
and just beef up the computer inside the analyzer (currently its a 2GHz
machine with only 256MB RAM)...but that'll be version 2.0 :-)

-- Dave


Tom Ellison said:
Dear Dave:

Good job! This is a viable alternative, but I was trying to give you
a simpler solution by first creating an index. I was saving this one
if you got no satisfaction from the indexing.

It is a shame that Jet's queries do not optimize for you. With an
imporved plan, it would have done what your query is now doing, but
without you having to change it to get the performance improvement.

MSDE would almost certainly have done this for you, and using Query
Analyzer you could learn easily what indexes would help. But you
should be alright for a while with what you have now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Solved it.

By first creating a query that returns only the max values for the rec_id
field (though certianly could have used the timestamp field as well) and
then creating another query that joins the first query to the historian
table based on the rec_id's. This returns the latest data and is nearly
instantaneous.

The first query is "Most Recent ID"
SELECT Historian.stream_name, Max(Historian.rec_id) AS MaxOfrec_id
FROM Historian
GROUP BY Historian.stream_name
ORDER BY Historian.stream_name;

The second query is "G2 Report Query"
SELECT [Most Recent ID].stream_name, Historian.timestamp, Historian.fe,
Historian.pb, Historian.zn, Historian.solids, Historian.result,
"PXDetail.aspx?rec_id=" & Format([rec_id]) AS url
FROM [Most Recent ID] INNER JOIN Historian ON [Most Recent ID].MaxOfrec_id =
Historian.rec_id;

Thanks again

Dave


Dear Dave:

From the Help in Access XP:

The First and Last functions are analogous to the MoveFirst and
MoveLast methods of a DAO Recordset object. They simply return the
value of a specified field in the first or last record, respectively,
of the result set returned by a query. 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.

end quote

As you have provided no sorting for the rows, just which row is
returned by First or Last is "arbitrary".

I suggest you want the MAX() function instead of the LAST() function.
You should use the Last function only in queries where you want
unpredictable results.

In short, the word "Last" may sound like what you want, but it isn't.
Last has nothing to do with chronology.

Now, if you want the other information (fe, pb. zn, solids, result,
and url) from the same row as that maximum date, we have some more
work to do! Changing these to MAX() will determine the maximum value
of each of these things irrespective of what row they are in, rather
than finding the ones in the MAX(timestamp) row.

How does this do?

SELECT stream_name, timestamp, fe, pb, zn, solids, result, url
FROM Historian T
WHERE timestamp = (SELECT MAX(timestamp) FROM Historian T1
WHERE T1.stream_name = T.stream_name);

Unlike the Last function which selects only one row, though it does so
arbitrarily, this query could return more than one row for each
stream_name if there are two rows with the same timestamp and they are
both the most recent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jan 2004 16:27:26 -0700, "Dave Taylor"

I have a database that records elemental analysis numbers from an X-ray
analyzer about once every 60 to 90 seconds, each record includes a record
ID
(sequential), timestamp, name of the stream being sampled, and the assay
values. I wrote a query that sorted the table by stream name and
timestamp
and then grouped by the stream name and pulled the last() values out for
each stream name. This seemed to work until yesterday, and now the
results
are all from January 19th and I *know* there is later data (i.e. I can
see
data in the main historian table from just a few minutes ago). I've
attached my SQL query text below and for the life of me cannot figure out
why in the heck it wont pull the latest values. Any help is greatly
appreciated. Is there a better way to achieve this same result?

Also, a bothersome quirk...when I save this query in Access, it always
converts the parenthesis to square brackets on the inner SELECT statement
and then complains about invalid bracketing...yet if I convert it back to
parenthesis, it works just fine (less the problem of not picking up the
LAST() record of the group). To get around this, I did try converting
the
inner SELECT to a named query, and that got rid of this issue, however it
still didnt pickup the LAST() record of the group.

Thanks

Dave Taylor


SELECT Historian.stream_name, Last(Historian.timestamp) AS [timestamp],
Last(Historian.fe) AS fe, Last(Historian.pb) AS pb, Last(Historian.zn) AS
zn, Last(Historian.solids) AS solids, Last(Historian.result) AS result,
"PXDetail.aspx?rec_id=" & Format(Last(Historian.rec_id)) AS url
FROM (SELECT * FROM Historian ORDER BY [stream_name], [timestamp]) AS
Historian
GROUP BY Historian.stream_name;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top