CODEWITHSUNDEEP
mysqlsql
Mr. Wolf
Mr. Wolf

Reputation: 29

SQL nested query alternative

SQL newbie here. I have a table where I have OrderID and State of the order.

OrderID, State, TimeStamp
1       0       20210502151515
1       1       20210502161616
1       2       20210502171717
2       0       20210502151617
2       1       20210502161718
2       3       20210502171819
3       0       20210502121617
3       4       20210502121718
4       0       20210502131617
5       0       20210502141718
6       0       20210502151515
6       2       20210502171717
7       0       20210502151515
7       1       20210502171717

Where 0 = OPEN, 1=Partially Completed, 2=Fully Completed, 3=Cancelled, 4=Rejected

I want to run a query where it would return orders that are OPEN (state=0) or Partially Completed (state=1). If the order is Fully completed, Cancelled or Rejected, I want to exclude those orders.

If I run to select orders with state 0,1 then it would return some orders that are fully done or cancelled or rejected. I need to run query where order states anything but 0 or 1.

I have this query which works but I am wondering if there is a better way to do it.

SELECT * 
  FROM myTable
 WHERE OrderID NOT IN (select OrderId from myTable where state not in (0, 1))

Thank you!

Upvotes: 0

Views: 67

Answers (4)

Warren
Warren

Reputation: 21

Might something like this work or would it be end up being too brutal as the recordset grows?

select Mytable.orderid, Mytable.State, Mytable.TimeStamp
from Mytable
inner join
    (
        select orderid, max(Timestamp) newesttimestamp
        from Mytable
        group by orderid
    ) newestorderdetails
    on Mytable.orderid = newestorderdetails.orderid and Mytable.Timestamp = newestorderdetails.newesttimestamp
where Mytable.state IN (0, 1)
order by Mytable.orderid, Mytable.state

Upvotes: 0

Chris Maurer
Chris Maurer

Reputation: 2580

Old-fashioned sql you would easily solve this with a correlated sub-query:

Select * from Mytable a
Where a.Timestamp=(Select max(Timestamp) from Mytable b
                   Where a.OrderId=b.OrderID)
  and state<2

This selects only the most recent record by order (max(Timestamp)) and further only keeps it if that most recent record is 0 or 1.

Upvotes: 0

Shen
Shen

Reputation: 267

There is a better way, but not with sql. Maybe you want to create another table to store the current state of the order. It is much easier to get what you want.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

If you just want orders, you can use aggregation:

select orderid
from mytable
group by orderid
having max(state) = 1;

If you want the details of the rows, you can use join, in or exists along with this query.

Upvotes: 1

Related Questions