SQL Query sort by in groups

Hi i need help with queries. here is my query & table structure.

SELECT *
FROM products_description a, products b
where a.products_id=b.products_id
AND b.products_status >0
AND a.products_name LIKE '%".$q."%'
ORDER BY b.products_quantity DESC LIMIT 10 


---------------------------------------------------
| products_id | products_name | products_quantity |
---------------------------------------------------
|     980     |       SOS     |        21         |
---------------------------------------------------
|     101     |       GOLD    |        9          |
---------------------------------------------------
|     232     |       BALL    |        1          |
---------------------------------------------------
|     422     |       SONG    |        O          | 
---------------------------------------------------
|     371     |       ALL     |        O 开发者_Python百科         | 
---------------------------------------------------
|     72      |       FISH    |        O          | 
---------------------------------------------------

I would like it to sort so that it is alphabaticaly ordered(product_name) while keeping those with 0 quantity at the bottom. Typically, i would like such result:

---------------------------------------------------
| products_id | products_name | products_quantity |
---------------------------------------------------
|     980     |       Ball    |        1          |
---------------------------------------------------
|     101     |       GOLD    |        9          |
---------------------------------------------------
|     232     |       SOS     |        21         |
---------------------------------------------------
|     422     |       All     |        O          | 
---------------------------------------------------
|     371     |       FISH    |        O          | 
---------------------------------------------------
|     72      |       SONG    |        O          | 
---------------------------------------------------

Appreciate any help!

Select ...
From products_description a
    Join products b
        On a.products_id=b.products_id
Where b.products_status >0
    And a.products_name LIKE '%".$q."%'
Order By Case When products_quantity = 0 Then 1 Else 0 End Asc
    ,  a.products_name Asc
Limit 10 

Try

SELECT *
FROM products_description a, products b
where a.products_id=b.products_id
AND b.products_status >0
AND a.products_name LIKE '%".$q."%'
ORDER BY (b.products_quantity = 0) DESC, products_name LIMIT 10

I am not sure at the moment if you need to order the boolean expression ASC or DESC so just try out.

If you're not limited to 10 items, you could try this:

SELECT *
FROM products_description a, products b
where a.products_id=b.products_id
AND a.products_quantity > 0
AND b.products_status >0
AND a.products_name LIKE '%".$q."%'
ORDER BY b.products_name, b.products_quantity DESC
UNION ALL
SELECT *
FROM products_description a, products b
where a.products_id=b.products_id
AND a.products_quantity = 0
AND b.products_status >0
AND a.products_name LIKE '%".$q."%'
ORDER BY b.products_name