The first thing you need to be able to do is group the data based on
stitems_ID
. You can assign each of the items in each "group" a row_number based on a sort order - in this case based on
stitemsu_UnitNum
ascending (as you want the two smallest measurements in each group).
Have a look at the results from this snippet
select stitems_ID, stitemsu_UnitName, stitemsu_UnitNum,
(@num:=if(@group = stitems_ID, @num +1, if(@group := stitems_ID, 1, 1))) row_number
from st_items t
CROSS JOIN (select @num:=0, @group:=null) c
order by stitems_ID, stitemsu_UnitNum asc , stitems_ID
Results
items_ID
stitemsu_UnitName stitemsu_UnitNum row_number
1 Piece 1 1
1 Doz 12 2
1 Box 36 3
2 Piece 1 1
Now all you need to do is have that as a sub-query and extract only the items that are less than or equal to 2 I.e.
select * FROM
(
select stitems_ID, stitemsu_UnitName, stitemsu_UnitNum,
(@num:=if(@group = stitems_ID, @num +1, if(@group := stitems_ID, 1, 1))) row_number
from st_items t
CROSS JOIN (select @num:=0, @group:=null) c
order by stitems_ID, stitemsu_UnitNum asc , stitems_ID
) as x
where x.row_number <= 2;
Credit: Adapted from a solution by Taryn @
here[
^]