sql query for selecting conditionally

I've been staring at this for too long and I can't get my head wrapped around this.

To simplify this, let's say I have a table that holds computer name, status, timestamp.  For example:

computer1 | ERROR | 4/7/2014 4:21 PM
computer1 | OK | 4/7/2014 4:22 PM
computer2 | OK | 4/7/2014 4:22 PM
computer2 | ERROR | 4/7/2014 4:23 PM

From this I want a list of computer that have a current status of ERROR.  If that computer has a more recent record with a status of OK then I don't want to see it.
LVL 10
bigbigpigAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The method below avoids any second reads of the original table:


SELECT [computer name]
FROM dbo.tablename
GROUP BY [computer name]
HAVING
    MAX(CASE WHEN status = 'ERROR' THEN timestamp END) =
    MAX(timestamp)
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select 'c1' computer, 'ERROR' stat, '4/7/2014 4:21:00 PM' dt into #temp

insert into #temp
select 'c1' computer, 'OK' stat, '4/7/2014 4:22:00 PM'

insert into #temp
select 'c2' computer, 'OK' stat, '4/7/2014 4:22:00 PM'

insert into #temp
select 'c2' computer, 'ERROR' stat, '4/7/2014 4:23:00 PM'

;with cte as
(select row_number() over(partition by computer order by dt desc) rowNm, *
 from #temp
 )
 select * from cte
 where RowNm = 1 and stat = 'ERROR'
0
 
Terry WoodsConnect With a Mentor IT GuruCommented:
I'd write it like this:

select computer_name, timestamp
from my_table as blah
where status = 'ERROR'
and timestamp = (select max(timestamp) from my_table where computer_name = blah.computer_name)

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PaulCommented:
no points pl.
I'd go with using row_number() as proposed by Kyle Abrahams, however it isn't necessary to use a CTE, this would do the same:
SELECT
      computer_name
    , timestamp
FROM (
            SELECT
                  computer_name
                , timestamp
                , ROW_NUMBER() OVER (PARTITION BY computer ORDER BY dt DESC) rowNm
            FROM #temp
      ) AS x
WHERE RowNm = 1
      AND stat = 'ERROR'
;

Open in new window

0
 
JoseBarrosoCommented:
Hi,

You can do like this but it's a little bit tricky, anyway for the tests i made it works:

SELECT Computer as XComputer, Max(DateZ+Status) AS MaxDateZ
FROM TABLE_
group by Computer
HAVING substring(Max(DateZ+Status),14,50)='ERROR';

The idea here is to join the date and the satus to get something like this:
20140408231010OK  - or -
20140408231010ERROR

this way you can use the MAX to get the last Status for that PC and then check if in that string exists the word "ERROR" with the function HAVING.

You will need to work the "Datez"  and format it in someting like "yyyyMMddhhmmss", check your regional options.

Fields: Computer, Status (ERROR/OK), DateZ (date)

Hoper this help
0
 
awking00Connect With a Mentor Commented:
I don't think the proposed solutions necessarily cover other potential scenarios, such as a single computer having a status of 'ERROR' or three rows in which the chronological statuses are 'ERROR','OK','ERROR'.
I think the following should work -
select computer_name, status, timestamp from
(select computer_name, status, timestamp,
 row_number() over (partition by computer_name order by timestamp desc) dt_rn,
 rank() over (partition by computer_name order by status desc) stat_rnk
 from tbl) as x
where dt_rn > 1 and stat_rnk = 2;
0
 
bigbigpigAuthor Commented:
Thanks everyone for the comments.  Apparently lots of ways to work this these all worked.
0
 
Scott PletcherSenior DBACommented:
>> I don't think the proposed solutions necessarily cover other potential scenarios, such as a single computer having a status of 'ERROR' or three rows in which the chronological statuses are 'ERROR','OK','ERROR'. <<

My code would handle either/both of those conditions.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
As would mine . . . I was surprised it wasn't excepted as a solution.
0
 
awking00Commented:
My apologies. I misread the question to mean show only those records with a status of 'ERROR' where a previous status was 'OK' and not just where the curent status was 'ERROR'. Forgive me.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
No issues awking :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.