CODEWITHSUNDEEP
sqlpostgresql
Puneet Pandey
Puneet Pandey

Reputation: 555

Select unique records based on multiple columns checks

I have a postgres table fetches with below columns

id  |  status  |  company_id  |  created_at
-------------------------------------------
10  |  success |  2773        |  2017-03-14
11  |  error   |  190         |  2017-03-02
12  |  error   |  303         |  2017-03-01
13  |  error   |  286         |  2017-02-16
14  |  error   |  2773        |  2016-12-05

I am trying to achieve below result:

id  |  status  |  company_id  |  created_at
-------------------------------------------
11  |  error   |  190         |  2017-03-02
12  |  error   |  303         |  2017-03-01
13  |  error   |  286         |  2017-02-16

And this is logic I am trying to apply:

  1. get all fetches where status is error.
  2. if result contains any company_id, for which latest fetch (by created_at) is success then skip it from final result set.
  3. company_id = 2773 will be skipped because it's latest fetch status is success

Please advise how can I accomplish this in SQL way.

Upvotes: 0

Views: 89

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use window functions for this:

select f.*
from (select f.*,
             sum( (status = 'success')::int) over (partition by company_id) as isSuccess
      fetches f
     ) f
where isSuccess = 0 and status = 'error';

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

seems you need rows for company that have no succes in status 

select * from my_table 
where company_id not in (select company_id from my_table where status = 'success')

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51456

try outer join:

with succ as (select company_id from fetches where status = 'success')
select fetches.* 
from fetches
left outer join succ on succ.company_id = fetches.company_id
where succ.company_id is null;

Upvotes: 1

Related Questions