Bassem Kam

Bassem Kam

  • 1.6k
  • 16
  • 541

how to create a MySQL View to select the smallest and largest Records?

Jul 31 2020 3:08 PM
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
7 4 u1 1 3
8 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 SmallUnit   LargeUnit SmallUnitPrice LargeUnitPrice   UnitNum 
1 u1 u2 2 20 10
2 u1 u2 5 60 12
3 u1 u1 10 10 1
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 :
 
stitems_ID SmallUnit LargeUnit SmallUnitPrice LargeUnitPrice UnitNum
1 u1 u3 2 200 100
2 u1 u2 5 60 12
3 u1 u1 10 10 1
4 u1 u4 3 300 100

Answers (3)