CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Golo Roden
Golo Roden

Reputation: 150962

How to select first item by group with condition?

I have a table with the following layout, to store orders for users, and to remember which orders are being processed right now:

Sequence | User | Order | InProcess
---------+------+-------+----------
       1 |    1 |     1 |
       2 |    1 |     2 |
       3 |    2 |     1 |
       4 |    3 |     1 |
       5 |    1 |     3 |
       6 |    4 |     1 |
       7 |    2 |     2 |

E.g., line 4 | 3 | 1 | means that the 4th order ever is for user 3, and it's his/her 1st order. Now I want to select the order which to process next. This has to be done according to the following criterias:

So, after some time this may look like this:

Sequence | User | Order | InProcess
---------+------+-------+----------
       1 |    1 |     1 | X
       2 |    1 |     2 |
       3 |    2 |     1 | X
       4 |    3 |     1 | X
       5 |    1 |     3 |
       6 |    4 |     1 |
       7 |    2 |     2 |

When now being asked for the next order to process, the answer would be the line with sequence number 6, since orders for users 1, 2 and 3 are already being processed, so no additional order for them may be processed. The question is: How do I get efficiently to this row?

Basically what I need is the SQL equivalent of

Of all orders, select the first order which is not in process, and whose user is not having an order already being processed.

The question is just how to tell this with SQL? BTW: I'm looking for a standard SQL solution, not DBMS-specific ways to go. However, if for whatever reason limiting the question to a specific DBMS, these are the ones I have to support (in this order):

Any ideas?

Upvotes: 0

Views: 851

Answers (2)

The Impaler
The Impaler

Reputation: 48875

You can get the next order to be processed by using the ROW_NUMBER() window function, as in:

select * 
from (
  select
    *,
    row_number() over(order by "order", "sequence") as as rn
  from t
  where "user" not in (
    select "user" from t where inprocess = 'X'
  )
) x 
where rn = 1

Available in PostgreSQL, MariaDB 10.2, MySQL 8.0, SQL Server 2012.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think captures your logic:

select t.*
from (select t.*, max(in_process) over (partition by user_id) as any_in_process
      from t
     ) t
where any_in_process is null
order by sequence
fetch first 1 row only;

Fetching one row is database specific, but the rest is pretty generic.

Upvotes: 1

Related Questions