I'm not sure I understand exactly what you are trying to do.

If you just want to query the table to get each WARNING/OK NOW pair then you can do something like this:

select topic, category, date
  from alarms
 order by topic, date
[download]

and you could process this in perl to merge matching rows:

my $data = $dbh->ct_sql($query);
my $previous_row;
foreach (@$data) {
    if($previous_row && $_->[0] eq $previous_row->[0]) {
        print "$previous_row->[0] $previous_row->[1] $previous_row->[2
+] $r_->[1] $r_->[2]\n";
        $previous_row = undef;
    } elsif($previous_row) {
        # There is no matching OK NOW row for this entry
        print "$previous_row->[0] $previous_row->[1] $previous\n";
    } else {
        $previous_row = $_;
    }
}
# print orphan, if there is one
if($previous_row) {
    print "$previous_row->[0] $previous_row->[1] $previous\n";
}
[download]

Not very pretty, but it should merge the two rows. Note that this will print all the rows - not just the latest instance for each topic.

If you want the latest entry for each topic, you can do something like this:

select id = max(id), topic, category, date = max(date)
  into #tmp
  from alarms
 group by topic, category

select t1.topic, t1.category, t1.date,
       t2.category, t2.date
  from #tmp t1,
       #tmp t2
 where t1.category = "WARNING"
   and t2.category = "OK NOW"
   and t1.topic = t2.topic
   and t1.date < t2.date
UNION
select t1.topic, t1.category, t1.date,
       NULL, NULL
  from #tmp t1,
       #tmp t2
 where t1.category = "WARNING"
   and t2.category = "OK NOW"
   and t1.topic = t2.topic
   and t1.date > t2.date
[download]

This will give you the latest row for each topic/category pair, with NULL values for the second category/date values if the last entry in the table is a WARNING.

There are probably other ways that this can be achieved at the SQL level...

Michael

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.