CODEWITHSUNDEEP
phpmysqlsql-order-byzero
PHP
PHP

Reputation: 159

Mysql order by: to skip zeros to end of list

$query = "SELECT products.*, login1.* 
          FROM products LEFT JOIN login1
               ON products.id_user = login1.id_user
          WHERE products.product LIKE '".$search_keyword."'
          ORDER BY products.product, products.rate "

When using this query the products with rate 0 list first, But I want that to display last, with no other change in the order. How can I achieve this.

Upvotes: 2

Views: 2181

Answers (2)

Aiias
Aiias

Reputation: 4748

Try this:

$query = "
  SELECT products.*, login1.*
  FROM products
  LEFT JOIN login1 ON products.id_user = login1.id_user
  WHERE products.product LIKE '" . $search_keyword . "'
  ORDER BY IF(products.rate = 0, 1, 0), products.product, products.rate "

This adds an initial check to the sort order so that products with a rate of 0 are listed after all products that have a rate not equal to 0.

Upvotes: 8

Jim
Jim

Reputation: 22656

Try this:

SELECT products.*, login1.* 
FROM products 
LEFT JOIN login1 ON products.id_user = login1.id_user 
WHERE products.product LIKE '".$search_keyword."' 
ORDER BY products.product, products.rate = 0,products.rate

It will first order by whether or not rate is 0. If it is 0 then this will get the value 1 and be placed at the end. The other values will then order by rate as usual.

Upvotes: 1

Related Questions