mysql - 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 select 1 row per productid minimum orderindex not rejected?

expected result:

 | id | productid | orderindex | rejected | ------------------------------------------ | 2  |  1        |   1        |   0      | | 4  |  2        |   0        |   0      | | 6  |  3        |   0        |   0      | 

i tried query, don't recieved correct result:

 select id, productid, min(orderindex) table rejected = 0 group productid 

this 1 don't work also:

 select id, productid, min(orderindex) (     select id, productid, orderindex     table     rejected = 0 ) t group productid 

you can start selecting minimum orderindex of products not rejected this:

select productid, min(orderindex) mytable rejected = 0 group productid; 

once have that, can join original table on condition productid , minorderindex match:

select m.id, m.productid, m.orderindex mytable m join(   select productid, min(orderindex) minorderindex   mytable   rejected = 0   group productid) tmp on tmp.productid = m.productid , tmp.minorderindex = m.orderindex; 

my query makes assumption there no duplicate (productid, orderindex) pairs. long don't exist, work fine. here sql fiddle example.