Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

EDIT

This query seems to work best so far.

select host,plugin_id,month,year, row_number() over (partition by plugin_id order by plugin_id) as plugin_split 
from table 
where risk in ('High') 
group by plugin_id,host,month,year 
order by plugin_id,host,month asc;

This seems to group all the plugin_id,host, and month together to make it easy to parse.

Would it be possible to only select hosts that have the same plugin_id for month 1 and 9? And then only for month 1 but not month 9? And then only for month 9 but not month 1? I was looking into parsing this with ruby if I couldn't do it in SQL. Not really a DBA any help would be appreciated. Thanks!

select host,risk,plugin_id,month,year from table where risk in ('High') group by plugin_id,host,risk,month,year;

     host      | risk | plugin_id | month | year 
---------------+------+-----------+-------+------
 172.20.10.122 | High |     10081 |     1 | 2014
 172.20.10.122 | High |     10081 |     9 | 2013
 172.20.10.51  | High |     10081 |     1 | 2014
 172.20.10.52  | High |     10081 |     1 | 2014
 172.20.10.53  | High |     10081 |     1 | 2014
 172.20.10.53  | High |     10081 |     9 | 2013
 172.20.10.54  | High |     10081 |     9 | 2013
 172.20.10.55  | High |     10081 |     9 | 2013
 172.20.10.56  | High |     10081 |     1 | 2014
 172.20.10.56  | High |     10081 |     9 | 2013
 172.20.10.57  | High |     10081 |     1 | 2014
 172.20.10.58  | High |     10081 |     1 | 2014
 172.20.10.58  | High |     10081 |     9 | 2013
 172.20.10.59  | High |     10081 |     1 | 2014
 172.20.10.59  | High |     10081 |     9 | 2013
 172.20.10.60  | High |     10081 |     1 | 2014
 172.20.10.60  | High |     10081 |     9 | 2013
 172.20.10.61  | High |     10081 |     1 | 2014
 172.20.10.61  | High |     10081 |     9 | 2013
 172.20.10.62  | High |     10081 |     1 | 2014
share|improve this question

2 Answers 2

You can self join the table so that on the left there are the hosts for month 1, on the right for month 9:

SELECT host, risk, plugin_id, month, year
  FROM your_table t1
  JOIN your_table t2 ON t1.month = 1 
                    AND t2.month = 9 
                    AND t1.host = t2.host 
                    AND t1.plugin_id = t2.plugin_id
 WHERE risk = 'High';

This will return the hosts where plugin_id is the same for the two months. It is not exactly clear what the other queries should be - I imagine it is not like the plugin is the same for a given month. Please edit your question with the clarification.

share|improve this answer

Try this

select host,risk,plugin_id, min(month) m1, max(month) m2 from table 
where risk in ('High') and month in (1,9)
group by plugin_id,host,risk
having count(distinct month)<=2
order by host,risk,plugin_id, m1, m2 desc
;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.