Most efficient SQL to select newest related record

[TheSmileyCoder]

I have a table of observations. This table is loaded as a recordset, and processed to be used in a Treeview control.

Part of this process is to determine which icon to display for the record in the treeview. The icon is determined by the status, and the status is determined by the latest related record in tbl_Updates.

Table Date:

tbl_Obs
PK_Obs (Autonumber)
tx_Obs (Text)
... (other fields)

tbl_Status
PK_Status (Autonumber)
tx_Status (Text)

tbl_Updates
PK_Update (Autonumber)
ID_Obs (Foreign key to PK_Obs, Long)
tx_Update
ID_Status (Foreign key to PK_Status)
dt_Date (Date/Time Timestamp of record creation)
... (other fields)

The Program flow:
A observation is created into tbl_Obs. At various points during the lifetime of a observation it will get updated and a new record is created in the update table each time. Update records are new er deleted or overwritten.

An example could be:
Observation (1):
Alignment of tracks is off by 2 cm

Updates:
2012-11-10 Open - Documentation Requested from supplier
2012-11-11 Open - Documentation Received from supplier
2012-11-13 Closed - Documentation evaluated and found satisfying.

The status of the observation is always determined by the latest update. So for the point of loading my information into the treeview I need a query giving me the PK_Obs, tx_Obs and the LATEST (based on date) ID_Status from the observations table.

Sure I can get this done by the use of multiple queries or subqueries, but I wonder what what would be the most efficient way to retrieve this? And why exactly is it the most efficient way?




The (a) subquery way:

SELECT tbl_Obs.PK_Observation, 
       tbl_Obs.mem_Observation, 
       (SELECT TOP 1 U.ID_Status 
            from tbl_ObsUpdate AS U 
            WHERE U.ID_Obs=PK_Observation 
            ORDER BY U.dt_Created DESC) AS Status
       FROM tbl_Obs

Creating an extra query:
I could create an extra query with the syntax:

SELECT   tbl_ObsUpdate.ID_Obs, 
         Last(tbl_ObsUpdate.ID_Status) AS LastOfID_Status
FROM     tbl_ObsUpdate
GROUP BY tbl_ObsUpdate.ID_Obs
ORDER BY Max(tbl_ObsUpdate.dt_Created) DESC;
 

And then join this to my main query.

[NeoPa]

I would expect the most efficient way to be the use of the subquery.

If my understanding of the question is correct in that it boils down to :
Is the subquery or the separate, pre-optimised, QueryDef the more efficient?

My reasoning for this is that with the subquery, the pre-optimisation of the SQL is all done together. That way, it is aware of the whole requirement and is not making assessments based on a scenario that is not its main requirement. In other words, when the QueryDef object is being optimised it will be done without any appreciation of what it's mainly used for. Does that make sense?

Ultimately, time and usage will tell of course ;-)

[TheSmileyCoder]

I am getting confused now. The Subquery I wrote is giving me trouble. The recordset loads fine, but when I reach the point where I loop through the recordset by using recordset.FindF irst I run into trouble with error 3354 "At Most One Record ca be returned by this Subquery).

I thought my subquery was returning just 1 record...

[NeoPa]

Not necessarily Smiley ;-)

The TOP n predicate actually returns n records in most circumstances, but where there are more than one nth records it will return all nth records as well as all others higher in the order.

[TheSmileyCoder]

Any recommendation on how to modify the subquery in the most efficient way?

[NeoPa]

If you have the possibility of multiple observations for the same date then I would definitely advise you to use a TimeStamp (Date and Time) field rather than a simple Date field. That obviates the problem (Causes it not to be an issue).

[Rabbit]

I have to disagree. I believe the separate query will be quicker. When the query that joins to the MAX query, I believe the engine will then be able to optimize them together.

The issue with the subquery in the SELECT clause is that it references the outer query. This means the subquery must be run multiple times, at least once for each distinct value referenced in the subquery.

Because you can have multiple updates with the same date, you would do something like this:

SELECT MAX(PK) AS MaxPK
FROM someTable a
INNER JOIN (
   SELECT 
      groupField,
      MAX(dateField) AS dt
   FROM someTable
   GROUP BY groupField
) t ON
   a.groupField = t.groupField AND
   a.dateField = t.dt

[NeoPa]

NeoPa:
If my understanding of the question is correct in that it boils down to :
Is the subquery or the separate, pre-optimised, QueryDef the more efficient?

Since my first response I noticed that this is certainly not a correct understanding in that the two different approaches are different in more ways than simply that. It seems Rabbit's correct in this, for the reason he explained, but frankly knowing him on SQL generally I'd take that as read anyway.

[TheSmileyCoder]

Sorry, my bad. The datetime field IS stored as date & time(though formatted to only display date).
I can't test the other replies now, but willl test it when I get back to my PC.

[NeoPa]

If it's already a TimeStamp then I would expect it to be very rare that the subquery would produce duplicates. See if you have any.

Smiley:
I need a query giving me the PK_Obs, tx_Obs and the LATEST (based on date) ID_Status from the observations table.

No wonder I was confused earlier. Can I take this to mean [ID_Status] should be from [tbl_ObsUpdate]?

As the subquery (that one particularly) approach is probably not the most efficient one anyway, I would consider something different :

SELECT   [ID_Obs]
       , Max([dt_Created]) AS [MaxUpdateDate]
FROM     tbl_ObsUpdate
GROUP BY [ID_Obs]

The table and field names might be awry. I checked with your table layouts, but the code you posted didn't match, so I used my best guesses. I mainly went with the code. This is very unlike you Smiley :-D Almost like a noob poster.

Anyway, moving on, with the query above, and I doubt it matters greatly whether it's a subquery or a separately defined QueryDef, you can link up to your main data thus (We'll refer to the above query as [subQ] for now.) :

SELECT tbl_Obs.*
     , tbl_ObsUpdate.*
FROM   ([tbl_Obs]
       INNER JOIN
       [tbl_ObsUpdate]
  ON   tbl_Obs.PK_Observation=tbl_ObsUpdate.ID_Obs)
       LEFT JOIN
       [subQ]
  ON   tbl_ObsUpdate.ID_Obs=subQ.ID_Obs
 AND   tbl_ObsUpdate.dt_Created=subQ.MaxUpdateDate

Hope this helps :-)