SQL Select only rows with a minimum value in a column with the Where clause

Question

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 for each product with a minimum ordinal index that is 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 did not get the correct result:

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

This doesn't work either:

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

Answer 1

You can start by choosing the minimum orderIndex products that are not rejected as follows:

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

After that, you can join it with the source table provided that productId and minOrderIndex correspond to:

 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 request assumes that there are no duplicate (productId, orderIndex) pairs. As long as this does not exist, everything will be fine. Here is an example of SQL Fiddle .

Answer 2

http://sqlfiddle.com/#!9/0196f/2

 SELECT DISTINCT t.* FROM table1 t INNER JOIN ( SELECT productId, min(orderIndex) minIdx FROM table1 WHERE rejected = 0 GROUP BY productId ) t1 ON t.productId = t1.productId AND t.orderIndex = t1.minIdx;