[thelist] SQL - searching a history table

Luther, Ron Ron.Luther at hp.com
Thu Oct 12 11:47:54 CDT 2006

Matt Warden asked about:

>>In english, I need the number of widgets with a widget status record
status 'X' and date_updated within the range 01-MAY-2006 and
>>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().}



More information about the thelist mailing list