CODEWITHSUNDEEP
sqlsql-server-2012
Ken
Ken

Reputation: 312

sql select row if another doesn't exist

I have a table with around one million entries that look like this:

TimeStamp                   TagName     Alarm Status
2017-08-02 10:53:10.000     XS-101      Alarm
2017-08-02 18:49:45.000     XS-201      Alarm
2017-08-03 01:08:16.000     XS-101      Normal
2017-08-05 09:16:42.000     XS-301      Alarm
2017-08-12 12:33:39.000     XS-101      Alarm

I need to figure out which TagName has been in alarm the longest, but I don't care if it's not currently in alarm. I can do this with with my program code, but all of my program's other SQL queries return exactly what I need. Is it possible to do this with only SQL?

I've searched around for examples where people are returning rows based on the contents of other rows, but I haven't had any luck.

Upvotes: 0

Views: 829

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Which tag is in alarm the longest is asking which tag has the oldest current alarm code.

You can do this with conditional aggregation:

select tagname, max(timestamp)
from t
group by tagname
having max(timestamp) = max(case when status = 'Alarm' then timestamp end)
order by max(timestamp) asc;

This assumes that two alarms are not in sequence for the same tag -- this is consistent with your described data.

Upvotes: 1

Related Questions