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

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?

CodePudding user response：

You can use the following query even if you are using MySQL v4 :

SELECT @M := MIN(`order`) FROM document_signature WHERE `status` = 'NOTSIGNED';
SELECT * FROM document_signature WHERE `status` = 'NOTSIGNED' AND `order` = @M;

CodePudding user response：

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