[thelist] SQL - searching a history table
Phil Turmel
philip at turmel.org
Thu Oct 12 15:37:26 CDT 2006
Matt Warden wrote:
> thelist,
>
> Suppose the following table structure:
>
> WIDGET
> ----------------------------
> id
> status
>
> WIDGET_STATUS
> ----------------------------
> widgetid
> status
> date_updated
>
> 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.
>
> Any ideas? I have something close now, but I will refrain from
> describing it to avoid boxing in creativity.
>
>
Hi Matt,
You didn't mention what DB platform, but they'll all be similar if they
support subqueries. Something like this:
Select status, Count(widgetid) As wcount
From (Select status, widgetid, Max(date_updated) As date_lastupdated
From WIDGET_STATUS
Where date_updated Between '20060501' And '20060601'
Group By status, widgetid)
Group By status
Order By wcount Desc;
Note that the 'WIDGET' table is superfluous here, and the
'date_lastupdated' column is discarded by the outer query. It could
just as easily been Min().
various SQL flavors support DISTINCT within a Count function, so you
could also try this:
Select status, Count(Distinct widgetid) As wcount
From WIDGET_STATUS
Where date_updated Between '20060501' And '20060601'
Group By status
Order By wcount Desc;
HTH,
Phil
More information about the thelist
mailing list