CODEWITHSUNDEEP
sql
Marcelo de Andrade
Marcelo de Andrade

Reputation: 332

Check value in rows

I have the table below

ID  | PARCEL  | STATUS  | ORDER_ID
1     1         PENDING      1234
2     2         COMPLETE     1234
3     1         COMPLETE     9999 
4     2         PENDING      9999   
5     3         PENDING      9999
6     1         COMPLETE     1111
7     2         COMPLETE     1111
8     3         COMPLETE     1111   
9     1         COMPLETE     3333 
10    2         PENDING      3333

i need get results when the first parcel is PENDING and have more than one parcel.

i try with the sql:

SELECT * FROM table WHERE parcela = 1 AND status = 'pending'
group by order_id
having count(order_id) > 1

the answer for the query is:

ID  | PARCEL  | STATUS  | ORDER_ID
1     1         PENDING      1234
2     2         COMPLETE     1234

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can get the orders using a having clause:

select order_id
from table 
group by order_id
having count(order_id) > 1 AND
       sum(case when parcela = 1 AND status = 'pending' then 1 else 0 end) > 0;

If you want the details, join the rows from the table back in on the order_id.

The problem with your query is that you are using a filter in the where clause that limits the rows to only the first row. This prevents the query from counting the total number of rows in each order.

Upvotes: 1

Related Questions