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

basimkamalandroid · August 1, 2020, 6:10am

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

m_hutley · August 1, 2020, 8:30am

Welcome to SitePoint

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:

How do you select the lowest unit value in the entire table?
How do you select the second record (and only the second record) of a query?
How do you select the lowest unit value for each grouping of rows by stitems_id?
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…)

basimkamalandroid · August 1, 2020, 2:06pm

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:

How do you select the lowest unit value in the entire table?
in the lowest unit row always stitemsu_UnitNum = 1
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)
How do you select the lowest unit value for each grouping of rows by stitems_id?
this point what i failed to achieve it
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.

system · October 31, 2020, 9:06pm

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

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

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

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