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

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
------------------------------------------------------------------------------------------------------------------```

Welcome to SitePoint :slight_smile:

These are both fairly easy, except for the ‘repeat only one’ bit of the first query.

Split the task up. I… can’t make a guess as to your level of SQL knowledge based on this post, so I’ll phrase this as a question to try and gauge/help, in order of challenge:

  1. How do you select the lowest unit value in the entire table?
  2. How do you select the second record (and only the second record) of a query?
  3. How do you select the lowest unit value for each grouping of rows by stitems_id?
  4. How do you join together results of two different queries?

(If you can answer all four of those, you should be able to get the answer to your query…)

first, thank for your reply.
i already tried many things but my query is very long so, i don’t know if i should to write it or no,
now , i think in two solution one of them as i described in the question to create 2 view. so for your qsts:

  1. How do you select the lowest unit value in the entire table?
    in the lowest unit row always stitemsu_UnitNum = 1
  2. How do you select the second record (and only the second record) of a query?
    i tried to select all rows that stitemsu_UnitNum != 1 and then sort it then take the first but i failed to do that because it get one row in all table. (i want one row per item)
  3. How do you select the lowest unit value for each grouping of rows by stitems_id?
    this point what i failed to achieve it
  4. How do you join together results of two different queries?
    i joined the st_items_units table two times with items table one of them to get the smallest unit row and the other one to get the other unit row.

i can’t update my Question so i will write what i did here.
this is first part of the query:

CREATE OR REPLACE VIEW `view_items_stock_per_store_with_small_large_unit` as
							  SELECT items.stitems_ID,
							  items.stitems_Status,
							  items.stitems_Name,
							  items.stitems_Type,
							  items.stitems_Pharma_ActiveIngredient,
							  items.stitems_Code,
                              items.stcate_ID,
                              items.stitems_Manufacturer,
                              items.stitems_EpireDateStatus,
						  	  items.stitems_MinBalance,
							  items.stitems_stagnant,
                              cat.stcate_Name,
							  manuf.manu_Name,
                              sunits.SmallUnitID,
                              sUName.Unit as SmallUnit,
							  lunits.LargeUnitID,
                              lUName.Unit as LargeUnit,
                              lunits.stitemsu_UnitNum,
                              lunits.stitemsu_PurchasesPrice as lPurchasesPrice, 
							  sunits.stitemsu_PurchasesPrice as sPurchasesPrice,
                              lunits.stitemsu_SalesPrice as lSalesPrice, 
                              sunits.stitemsu_SalesPrice as sSalesPrice,
                              (CASE 
									WHEN items.stitems_Type  = 0 THEN COALESCE(ca.Amount, 0)
									WHEN items.stitems_Type = 1 THEN COALESCE(pa.Amount,0)
								END) AS stock,
                                 (CASE 
									WHEN items.stitems_Type  = 0 THEN  ca.StoreID
									WHEN items.stitems_Type = 1 THEN  pa.ss_StoreID
								END) AS storeID,
                                (CASE 
									WHEN items.stitems_Type  = 0 THEN  ca.store_Name
									WHEN items.stitems_Type = 1 THEN  pa.store_Name
								END) AS store_Name,
                                 (CASE 
									WHEN items.stitems_Type  = 0 THEN COALESCE(ca.TotalCost, 0)
									WHEN items.stitems_Type = 1 THEN COALESCE(pa.TotalCost,0)
								END) AS totalCost,
                              (CASE 
									WHEN items.stitems_Type  = 0 THEN COALESCE(ca.TotalCost, 0) / COALESCE(ca.Amount, 0)
									WHEN items.stitems_Type = 1 THEN COALESCE(pa.TotalCost,0) / COALESCE(pa.Amount,0)
								END) AS unitCost
					   FROM st_items items
                       LEFT JOIN (SELECT 
							  sunit.stitems_ID,
							  sunit.stitemsu_UnitName as SmallUnitID,
                              sunit.stitemsu_PurchasesPrice,
                              sunit.stitemsu_SalesPrice,
                              sunit.stitemsu_UnitNum
								  FROM st_items_units sunit
                                  where sunit.stitemsu_UnitNum =1 
								  GROUP BY sunit.stitems_ID,sunit.stitemsu_UnitName,sunit.stitemsu_PurchasesPrice,sunit.stitemsu_SalesPrice, sunit.stitemsu_UnitNum) sunits
					   ON sunits.stitems_ID = items.stitems_ID
                       
                       LEFT JOIN (SELECT 
							  lunit.stitems_ID,
							  lunit.stitemsu_UnitName as LargeUnitID,
                              lunit.stitemsu_PurchasesPrice,
                              lunit.stitemsu_SalesPrice,
                              lunit.stitemsu_UnitNum
								  FROM st_items_units lunit
								  where stitemsu_UnitNum IN (SELECT MAX(stitemsu_UnitNum))
								  GROUP BY lunit.stitems_ID,lunit.stitemsu_UnitName,lunit.stitemsu_PurchasesPrice,lunit.stitemsu_SalesPrice, lunit.stitemsu_UnitNum
                                  order by lunit.stitemsu_UnitNum asc ) lunits
					   ON lunits.stitems_ID = items.stitems_ID
                       

my second idea is to convert the rows to columns like this table:

stitems_ID 1stU 1ndU Num 1ndU Price 2stU 2ndU Num 2ndU Price 3stU 3ndU Num 3ndU Price 4stU 4ndU Num 4ndU Price
1 u1 1 2 U2 10 20 U3 100 200 U3 100 200
2 u1 1 5 U2 12 60 U2 12 60 U2 12 60
3 u1 1 10 u1 1 10 u1 1 10 u1 1 10
4 u1 1 3 U3 12 36 U4 100 300 U7 200 600

and

  • if the item have only one unit fill the 1st & 2nd , 3rd , 4th with 1st data
  • if the item have only 2 unit fill the 2nd , 3rd , 4th with 2st data
  • if the item have only 3 unit fill the 3rd , 4th with 3st data.

but i don’t know how to do that.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.