Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find first occurrence of several items (A2003)

    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...
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find first occurrence of several items (A2003)

    Try

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

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find first occurrence of several items (A2003)

    Very cool. Thank you!
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find first occurrence of several items (A2003)

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

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find first occurrence of several items (A2003)

    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.Machine ID, Min(Abs(DateDiff("s",qprgetvalue("DisplayGPSDate") ,timetag))) AS DeltaSeconds
    FROM qrySelectedGPSPosWithinWindowOfDisplayDate
    GROUP BY qrySelectedGPSPosWithinWindowOfDisplayDate.Machine ID;

    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.Machine ID 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("D isplayGPSDate")) And DateAdd("h",qprgetvalue("DisplayWindowHours"),qprg etvalue("DisplayGPSDate"))));

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

    Thanks for your help.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find first occurrence of several items (A2003)

    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?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find first occurrence of several items (A2003)

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •