i have a table contains items units details ( st_items_units ) like :-
-------------------------------------------------------
id ------ stitems_ID ------unit------ stitemsu_UnitNum --- unit price -----
1 ------ 1 ------u1 ------ 1 ------------------ 2 --------------
2 ------ 1 ------u2 ------ 10 ----------------- 20 -------------
3 ------ 1 ------u3 ------ 100 ---------------- 200 ------------
4 ------ 2 ------u1 ------ 1 ------------------ 5 --------------
5 ------ 2 ------u2 ------ 12 ----------------- 60 -------------
6 ------ 3 ------u1 ------ 1 ------------------ 10 -------------
8 ------ 4 ------u1 ------ 1 ------------------ 3 --------------
9 ------ 4 ------u3 ------ 12 ----------------- 36 -------------
9 ------ 4 ------u4 ------ 100 ---------------- 300 ------------
----------------------------------------------------------------------------
as the table every item may be have only one unit or 2 unit or 3 unit or more. i want to create 2 view :
1- to select the two smallest unit (if there is one unit for the item , repeat the only one ). as this table :
-------------------------------------------------------
stitems_ID ---- stitems_Name ---- SmallUnit ---- LargeUnit ------ SmallUnitPrice ---- LargeUnitPrice ---- UnitNum
1 ---- item 1 ---- u1 ----------- u2 ------ 2 ---- 20 ---- 10
2 ---- item 2 ---- u1 ----------- u2 ------ 5 ---- 60 ---- 12
3 ---- item 3 ---- u1 ----------- u1 ------ 10 ---- 10 ---- 1
4 ---- item 4 ---- u1 ----------- u3 ------ 3 ---- 36 ---- 12
------------------------------------------------------------------------------------------------------------------
2- to select the smallest unit & the largest Unit (if there is one unit for the item , repeat the only one ). as this table :
1 ---- item 1 ---- u1 ----------- u3 ------ 2 ---- 200 ---- 100
2 ---- item 2 ---- u1 ----------- u2 ------ 5 ---- 60 ---- 12
3 ---- item 3 ---- u1 ----------- u1 ------ 10 ---- 10 ---- 1
4 ---- item 4 ---- u1 ----------- u4 ------ 3 ---- 300 ---- 100
------------------------------------------------------------------------------------------------------------------```