Find first occurrence of several items (A2003)

Topic

New Reply

Given the following table/query structure (simplified structure and contrived data), is there a way to find the occurrence of each MachineID’s StartTime and Status when the AbsoluteOffsetMinutes is minimized? In the example data, the AbsoluteOffsetMinutes is a calculated query field that shows the difference between the StartTime and Jan 27 9:00 AM. The Status is the state of the machine when the reading was taken.

ID MachineID StartTime AbsoluteOffsetMinutes Status
1 1 27/01/2007 8:00:00 AM 60 1
2 1 27/01/2007 8:10:00 AM 50 2
3 1 27/01/2007 8:30:00 AM 30 3
4 1 27/01/2007 8:55:00 AM 5 4
5 1 27/01/2007 9:07:00 AM 7 1
6 1 27/01/2007 9:30:00 AM 30 2
7 2 27/01/2007 8:35:00 AM 35 10
8 2 27/01/2007 8:50:00 AM 10 20
9 2 27/01/2007 9:15:00 AM 15 30
10 2 27/01/2007 9:30:00 AM 30 40

With the example data, I want the resulting query to display all the fields from record ID’s 4 and 8 (status = 4 and 20 respectively) (there are several other fields in the real table structure that also need to be displayed). I’ve been able to get the correct result by using two queries, but I am looking for a single-query solution. I recently read about using the TOP 1 clause in a query, but haven’t been able to make that work…

Reply | Quote

Replies

Try

SELECT * FROM TableName WHERE AbsoluteOffsetMinutes In (SELECT Min(t.AbsoluteOffsetMinutes) FROM TableName AS t WHERE t.MachineID = TableName.MachineID GROUP BY t.MachineID)

Reply | Quote

Very cool. Thank you!

Reply | Quote

But keep in mind that if there are multiple records with the minimum value for the same MachineID, they will all be returned.

Reply | Quote

As I read thru the SQL, I thought there may be an issue with duplicated values. Experimented with the contrived data using duplicates values for different machines, and it worked properly. Hadn’t considered the case of duplicated values for a single machine, but the data are generated from a GPS device that creates a maximum of one record per second, so duplicates within a single machine are “impossible” .

Then I ran the function on some real data. About 54000 records, with 3 “target” records to be returned. My two-query solution returned the targets virtually instantaneously, while the new SQL took anywhere from 35 to 65 seconds, depending on the size of the “window” that I selected around the target date. I will look into it further to see if I can optimize, but I suspect that Jet is performing some calculations on the entire table, rather than selecting first (where it can), then calculating. The offset is a calculated value.

If you are curious, here is my two query solution using the SQL from the actual tables:

SELECT GPSPos.*
FROM GPSPos INNER JOIN qryMinimumTimes AS q ON GPSPos.MachineID=q.MachineID
WHERE (((Abs(DateDiff(“s”,qprgetvalue(“DisplayGPSDate”),[timetag])))=[deltaseconds]));

where qryMinimumTimes is:

SELECT qrySelectedGPSPosWithinWindowOfDisplayDate.MachineID, Min(Abs(DateDiff(“s”,qprgetvalue(“DisplayGPSDate”),timetag))) AS DeltaSeconds
FROM qrySelectedGPSPosWithinWindowOfDisplayDate
GROUP BY qrySelectedGPSPosWithinWindowOfDisplayDate.MachineID;

And here is the SQL of your function using the real query and field names:

SELECT *
FROM qrySelectedGPSPosWithinWindowOfDisplayDate
WHERE OffsetSeconds In (SELECT
Min(t.OffsetSeconds) FROM qrySelectedGPSPosWithinWindowOfDisplayDate AS t WHERE t.MachineID =
qrySelectedGPSPosWithinWindowOfDisplayDate.MachineID GROUP BY t.MachineID);

Finally, here is the SQL of qrySelectedGPSPosWithinWindowOfDisplayDate, which selects the records that are within a specified time window of the target date:

SELECT mqryGPSPosSelected.*, Abs(DateDiff(“s”,[timetag],qprgetvalue(“DisplayGPSDate”))) AS OffsetSeconds
FROM mqryGPSPosSelected
WHERE (((mqryGPSPosSelected.TimeTag) Between DateAdd(“h”,-1*qprgetvalue(“DisplayWindowHours”),qprgetvalue(“DisplayGPSDate”)) And DateAdd(“h”,qprgetvalue(“DisplayWindowHours”),qprgetvalue(“DisplayGPSDate”))));

In these statements, the qprgetvalue function returns some constant values.

Thanks for your help.

Reply | Quote

Nested queries are almost always less efficient than using two queries.
With two queries, the Jet engine exexutes qryMinimumTimes once, then joins it to the table once. With nested queries, the subquery must be executed for each record in the table.
Do you have a particular reason for not wanting to use two queries?

Reply | Quote

OK – I understand better now.

No particular reason except several databases I’ve been working on lately have been getting unwieldly with the number of queries, and I wondered whether there was a way to eliminate some of them. I was also wondering whether a single query would be more efficient than multiple queries, but you’ve pointed out the error in that line of thinking.

Reply | Quote