Question 112808 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

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

    SELECT d.* FROM document_signature AS d, (
  SELECT id AS min_id, MIN(`order`) AS min_o FROM document_signature WHERE `status` = 'NOTSIGNED' GROUP BY document_id
) AS t WHERE d.id = t.min_id and d.`order`= t.min_o
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search