Search code examples
mysqlgreatest-n-per-group

SQL Select only rows with Minimum Value on a Column with Where Condition


Table:

| id | productId | orderIndex | rejected |
------------------------------------------
| 1  |  1        |   0        |   1      |
| 2  |  1        |   1        |   0      |
| 3  |  1        |   2        |   0      |
| 4  |  2        |   0        |   0      |
| 5  |  2        |   1        |   1      |
| 6  |  3        |   0        |   0      |

How can I select one row per productId with minimum orderIndex that not rejected?

Expected result:

| id | productId | orderIndex | rejected |
------------------------------------------
| 2  |  1        |   1        |   0      |
| 4  |  2        |   0        |   0      |
| 6  |  3        |   0        |   0      |

I tried this query, but don't recieved correct result:

SELECT id, productId, min(orderIndex)
FROM table
WHERE rejected = 0
GROUP BY productId

This one don't work also:

SELECT id, productId, min(orderIndex)
FROM (
    SELECT id, productId, orderIndex
    FROM table
    WHERE rejected = 0
) t
GROUP BY productId

Solution

  • You can start by selecting the minimum orderIndex of products that are not rejected like this:

    SELECT productId, MIN(orderIndex)
    FROM myTable
    WHERE rejected = 0
    GROUP BY productId;
    

    Once you have that, you can join it with your original table on the condition that productId and minOrderIndex match:

    SELECT m.id, m.productId, m.orderIndex
    FROM myTable m
    JOIN(
      SELECT productId, MIN(orderIndex) AS minOrderIndex
      FROM myTable
      WHERE rejected = 0
      GROUP BY productId) tmp ON tmp.productId = m.productId AND tmp.minOrderIndex = m.orderIndex;
    

    My query makes the assumption that there are no duplicate (productId, orderIndex) pairs. As long as those don't exist, this will work just fine. Here is an SQL Fiddle example.