[thelist] SQL - searching a history table

[Luther, Ron]

Matt Warden asked about:

>>In english, I need the number of widgets with a widget status record
for 
status 'X' and date_updated within the range 01-MAY-2006 and
01-JUN-2006. 
>>The catch is that this must be the last update to the status in that
time period.


Hi Matt,

Smells like a subquery to me.  Unfortunately not my area of expertise.
;-(
I'll guess something like:

SELECT Count(widget)
 WHERE Thingie = (SELECT widget, status, MAX(date_updated) FROM tables
	WHERE date_updated BETWEEN 01-May and 01-Jun)

{The Max() should eliminate the need for a Distinct().}


HTH,

RonL.