SQL ORDER results BY multiple clauses

Hi people I work with prestashop and have this long query that pulls data from different tables join them together and begins like this:

SELECT p.*, product_shop.*, 
       stock.out_of_stock, 
       IFNULL(stock.quantity, 0) as quantity, 
       product_attribute_shop.minimal_quantity AS
      ... etc etc

And at the end is ordered BY let's say name and limited (for pagination)

      ORDER BY pl.`name` asc
      LIMIT 0,9

I want to add a quantity clause but If I

    ORDER BY quantity desc, pl.`name` asc
    LIMIT 0,9

Results are now ordered by quantity first and then by name. What I am trying to achieve is to order it also by 'quantity'. But what I need is just to put the products with 0 quantity at the end.

I explain myself better:

I want to see all the products ordered by name BUT the ones with 0 quantity, I need them at the end (of course also ordered by name). Lets say I have 14 products:

product A: 752
product B: 0
product C: 1000
product D: 584
product E: 333
product F: 264
product G: 0
product H: 6
product I: 0 product J: 994
product K: 0
product L: 2
product M: 4500
product N: 7

I need to order in a way that if I

LIMIT 0,9 Result is: A,C,D,E,F,H,J,L,M

LIMIT 0,14 Result is: A,C,D,E,F,H,J,L,M,N,B,G,I,K

Thank you

Solution

You want to order by just a boolean -- whether the values is zero or not. In MySQL you can use boolean expressions, with "1" for true and "0" for false:

ORDER BY (quantity = 0),  -- put 0 last
         pl.`name` asc