Page 1 of 1

Tricky SQL question -- distinct maybe?

Posted: Fri Aug 06, 2010 11:37 am
by xterra
My table may look like this :

Serial | Status | Report
--------------------------------------------
100 | Online | 1
100 | Offline | 2


What I would like to do is generate a "yes to no" report, meaning, list all devices that were online on the previous report and are offline on the current report. Obviously the most current report will have the highest number (MAX) and the previous will be MAX()-1. This is what I have so far:

Code: Select all

SELECT * 
FROM table where status like 'online' and rid in (SELECT MAX(rid)-1 from table) 
This so far succesfully lists all devices that are online in the previous report, but how do I make it then say "AND are offline now"?

Any help is greatly appreciated. My boss is expecting this asap ha.

Re: Tricky SQL question -- distinct maybe?

Posted: Fri Aug 06, 2010 11:52 am
by xterra
I'm closer:

Code: Select all

select serial as serialkey from stip where status like 'Online' and rid in (SELECT MAX(rid) from stip ) and (status like 'Offline' and rid in (SELECT MAX(rid)-1 from stip ));