Mysql : how to select the second row if there are more than one record and select the first if there only one record?

Question

0.00/5 (No votes)

See more:

hi,
i have some items in st_items table, and its units stored in st_items_units as this table (show the units of item (1) and item (2):

stitemsu_ID -- stitems_ID -- stitemsu_UnitName -- stitemsu_UnitNum --
1 ------------ 1 ----------- Piece -------------- 1 -----------------
2 ------------ 1 ----------- Doz   -------------- 12 ----------------
3 ------------ 1 ----------- Box   -------------- 36 ----------------
4 ------------ 2 ----------- Piece -------------- 1 -----------------

as the table item 1 have 3 units and item 2 have only one.
i need to create MySQL VIEW that show its smallest two units (1st and 2nd) based on the number of piece in every one (stitemsu_UnitName), so the result will be like that :

stitems_ID -- SmallUnit -- LargeUnit -- stitemsu_UnitNum ---
1 ----------- Piece ------ Doz -------- 12 -----------------
2 ----------- Piece ------ Piece ------ 1  -----------------

What I have tried:

i tried this Code , and it show the first unit (Piece) successfully, but i failed to show the second one.

LEFT JOIN (SELECT
                             lunit.stitems_ID,
                             lunit.stitemsu_UnitName as LargeUnitID,
                             lunit.stitemsu_UnitNum
                                 FROM st_items_units lunit
                                 GROUP BY lunit.stitems_ID order by lunit.stitemsu_UnitNum desc limit 1,1) lunits
                      ON lunits.stitems_ID = items.stitems_ID

please check the full code that i wrote it.

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,
                              cat.stcate_Name,
							  manuf.manu_Name,
                              sunits.SmallUnitID,
                              sUName.Unit as SmallUnit,
							  lunits.LargeUnitID,
                              lUName.Unit as LargeUnit,
                              lunits.stitemsu_UnitNum,
                              (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,
                                stores.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_UnitNum
								  FROM st_items_units sunit
                                  where sunit.stitemsu_UnitNum =1
								  GROUP BY sunit.stitems_ID) sunits
					   ON sunits.stitems_ID = items.stitems_ID
                       LEFT JOIN (SELECT 
							  lunit.stitems_ID,
							  lunit.stitemsu_UnitName as LargeUnitID,
                              lunit.stitemsu_UnitNum
								  FROM st_items_units lunit
								  GROUP BY lunit.stitems_ID order by lunit.stitemsu_UnitNum desc limit 1,1) lunits
					   ON lunits.stitems_ID = items.stitems_ID


// ... the reset of the code

Posted 3-Feb-20 9:16am

Golden Basim

Updated 17-Sep-22 6:21am

v4

Add a Solution

Comments

CHill60 4-Feb-20 6:52am

I suggest you remove all the code that is not relevant to the problem you have stated - no-one is going to read through all of that

Golden Basim 4-Feb-20 12:39pm

i already did that i write the both

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2020-02-05T04:17:00

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

SQL

 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.

SQL

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[^]