CODEWITHSUNDEEP
phpmysqlmysqlisql-order-by
kira423
kira423

Reputation: 427

Show rows with value greater than 0 then show rows that equal 0

This is my query

$offers_with_limit = $db->query("
SELECT *
FROM `offers`
WHERE `countries` LIKE '%'?'%'
  AND `category` = '$query2'
  AND NOT EXISTS
    (SELECT *
     FROM completed
     WHERE completed.offer_id = offers.id
       AND completed.user = ?)
  AND NOT EXISTS
    (SELECT *
     FROM pending
     WHERE pending.offer_id = offers.id
       AND pending.user = ?)
  AND NOT EXISTS
    (SELECT *
     FROM ignored
     WHERE ignored.offer_id = offers.id
       AND ignored.user = ?)
  AND `active` = '1'
  AND `hard_incent` > '0'
ORDER BY `hard_incent` ASC,
         `date_added` DESC LIMIT $startpoint,$limit
    ");

Obviously it is only going to show the rows that are greater than 0 but I also need it to continue to show the rows that are equal to 0 after the rows that are greater than if I don't add the hard_incent > '0' then it shows the rows that are equal to 0 first because they are in ASC order.

Upvotes: 0

Views: 67

Answers (1)

Barmar
Barmar

Reputation: 781804

Use:

ORDER BY hard_incent <= 0, hard_incent ASC, date_added DESC

The first comparison will be 0 for rows that are greater than 0, 1 for rows that are less than or equal to 0, so it will show the greater rows first. Then within those two sets it will order by hard_incent and date_added.

Upvotes: 1

Related Questions