Select d.* from
( select agent_addr, max(date_time) from eventlog where priority = 1
group by agent_addr) d,
(select agent_addr, max(date_time) from eventlog where priority = 6
group by agent_addr) u
where d.agent_addr = u.agent_addr
and d.access_time >= u.access_time
The above statement is to get certain data from an event table. I need
all of the lines where the event is 1 and there are no newer event 1's
or event 6's
I need to know what devices are currently down (Priority 1) based on
this history table. where there is no newer event 1 entries for the
agent_addr, or there is no newer priority 6's for that agent_addr
(priority 6 means device is back up)
I'm stumped on the syntax to do joins, or to get this to work.
the table works like the following
device goes down entry is made as priority 1 eventid 1
device comes back up new endtry is made priority 6 eventid 2
device goes back down another entry is made as priority 1. eventid 3
I need to make sure i only am getting a list of the latest status of
each device in the aformentioned scenario it would be the one with
event ID 3
I really appreciate any assistance at all. Thanks in advance
Joshua G