Need help with an sql please

I'm trying to move data from an active monitoring database to a historical database and its been too long since I've done this type of query. I appreciate any help.

The events are entered into the history table by node_id, application, message_group, object, creation_time, and severity. My first thought was to do:

select a.message_number
from opc_op.opc_hist_messages a, opc_op.opc_hist_messages b where a.node_id = b.node_id

	and a.application = b.application
	and a.message_group = b.message_group
	and a.object = b.object
	and trunc(a.local_creation_time) = trunc(sysdate - 1)
	and a.local_creation_time > b.local_creation_time
	and a.severity != b.severity
order by a.node_id, a.application, a.message_group,
	a.object, a.local_creation_time, a.severity;

Then someone else suggested I use min() (which I forgot about):

select a.node_id, a.application, a.message_group,

        a.object, min(a.local_creation_time), a.severity from opc_op.opc_hist_messages a
group by a.node_id, a.application, a.message_group,

        a.object, a.local_creation_time, a.severity

What I need are only the threshold events, when the monitored item changes severity from say normal to critical. How long it was normal or critical will be implied by the next change in severity. I don't need to keep all the intervening records to prove its still at the same state.

Can someone help? My head hurts!

Mike Received on Wed Oct 24 2001 - 17:21:53 CEST