mysqlsqlsubquerycorrelated-subquery

The Rows Holding the Group-wise Minimun of a Certain Column, but using an additional condition


From this dataset:

mysql> SELECT * FROM document_signature;
+----+-------------+-------------+---------+-------+-----------+
| id | document_id | employee_id | user_id | order | status    |
+----+-------------+-------------+---------+-------+-----------+
|  1 |           1 |           2 |    NULL |     0 | SIGNED    |
|  2 |           1 |           3 |    NULL |     1 | NOTSIGNED |
|  3 |           1 |           4 |    NULL |     1 | NOTSIGNED |
|  4 |           2 |           3 |    NULL |     0 | NOTSIGNED |
|  5 |           3 |        NULL |       1 |     0 | SIGNED    |
|  6 |           3 |           1 |    NULL |     0 | NOTSIGNED |
+----+-------------+-------------+---------+-------+-----------+
6 rows in set (0.00 sec)

I want to find the rows that have the minimun order, but only from those whose status is NOTSIGNED, even if there is more than one for each document_id

Using this query:

SELECT s.*
FROM document_signature s
WHERE `order` =
      (SELECT MIN(s2.`order`)
       FROM document_signature s2
       WHERE s.document_id = s2.document_id
         AND s2.status = 'NOTSIGNED');

These are the results I'm getting:

+----+-------------+-------------+---------+-------+-----------+
| id | document_id | employee_id | user_id | order | status    |
+----+-------------+-------------+---------+-------+-----------+
|  2 |           1 |           3 |    NULL |     1 | NOTSIGNED |
|  3 |           1 |           4 |    NULL |     1 | NOTSIGNED |
|  4 |           2 |           3 |    NULL |     0 | NOTSIGNED |
|  5 |           3 |        NULL |       1 |     0 | SIGNED    |
|  6 |           3 |           1 |    NULL |     0 | NOTSIGNED |
+----+-------------+-------------+---------+-------+-----------+
5 rows in set (0.00 sec)

My question is: Why is there a row with status SIGNED in the resultset, what am I doing wrong here?


Solution

  • Although, both document_id=3 have different status, they have the same order value. Therefore, your MIN() result is actually true for both status. The quick way workaround here is to add another status='NOSIGNED' condition like so:

    SELECT s.*
    FROM document_signature s
    WHERE `order` =
          (SELECT MIN(s2.`order`)
           FROM document_signature s2
           WHERE s.document_id = s2.document_id
             AND s2.status = 'NOTSIGNED')
      AND s.status='NOTSIGNED'; /*add here*/
    

    Demo fiddle

    There is actually more than one way to do this on MySQL v8+, here is one:

    SELECT *
      FROM
    (SELECT *,
           MIN(`order`) OVER (PARTITION BY document_id) AS min_ord
       FROM document_signature
      WHERE status='NOTSIGNED') s
    WHERE s.`order`=s.min_ord;
    

    On your current sample data, this may look no different but let's say if we add two more rows in the table:

    INSERT INTO `document_signature` VALUES
    (7,1,3,NULL,2,'NOTSIGNED'),
    (8,2,4,NULL,2,'NOTSIGNED');
    

    it will become like this:

    +----+-------------+-------------+---------+-------+-----------+
    | id | document_id | employee_id | user_id | order | status    |
    +----+-------------+-------------+---------+-------+-----------+
    |  1 |           1 |           2 |    NULL |     0 | SIGNED    |
    |  2 |           1 |           3 |    NULL |     1 | NOTSIGNED |
    |  3 |           1 |           4 |    NULL |     1 | NOTSIGNED |
    |  4 |           2 |           3 |    NULL |     0 | NOTSIGNED |
    |  5 |           3 |        NULL |       1 |     0 | SIGNED    |
    |  6 |           3 |           1 |    NULL |     0 | NOTSIGNED |
    |  7 |           1 |           3 |    NULL |     2 | NOTSIGNED |
    |  8 |           2 |           4 |    NULL |     2 | NOTSIGNED |
    +----+-------------+-------------+---------+-------+-----------+
    

    Then, when you run the subquery of:

    SELECT *,
           MIN(`order`) OVER (PARTITION BY document_id) AS min_ord
       FROM document_signature
      WHERE status='NOTSIGNED'
    

    you'll get this result:

    +----+-------------+-------------+---------+-------+-----------+---------+
    | id | document_id | employee_id | user_id | order | status    | min_ord |
    +----+-------------+-------------+---------+-------+-----------+---------+
    |  2 |           1 |           3 |    NULL |     1 | NOTSIGNED |    1    |
    |  3 |           1 |           4 |    NULL |     1 | NOTSIGNED |    1    |
    |  4 |           2 |           3 |    NULL |     0 | NOTSIGNED |    0    |
    |  6 |           3 |           1 |    NULL |     0 | NOTSIGNED |    0    |
    |  7 |           1 |           3 |    NULL |     2 | NOTSIGNED |    1    |
    |  8 |           2 |           4 |    NULL |     2 | NOTSIGNED |    0    |
    +----+-------------+-------------+---------+-------+-----------+---------+
    

    Notice that for id = 7 & 8, the value in min_ord is not the same as their order value. That's when you make that query as derived table and do the WHERE s.order=s.min_ord comparison.

    There's another example in this fiddle using cte