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
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.