Tricky SQL question -- distinct maybe?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Tricky SQL question -- distinct maybe?

Post 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.
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Re: Tricky SQL question -- distinct maybe?

Post 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 ));
Post Reply