very specific query with specific ordering

[drooh]

Ok, the main question is to use multiple queries or is there one that can be written to accomplish the task.

Here is the scenario:
Real Estate agency with listings of homes on their site. Displays info about houses including price, location, sold, pending etc.

I would like to order the results in this way
overall by price with most expesnive at top
then - all available followed by pending and then sold

In the database there are fields for pending & sold, if pending=1 then it is true and if 0 false, same with sold
So for those queries I would assume something like this

// available
$sql = "SELECT * FROM `listings` WHERE `pending` = 0 && `sold` = 0 ORDER BY `price` DESC";
// pending
$sql = "SELECT * FROM `listings` WHERE `pending` = 1 && `sold` = 0 ORDER BY `price` DESC";
// sold
$sql = "SELECT * FROM `listings` WHERE `pending` = 0 && `sold` = 1 ORDER BY `price` DESC";

Is there a way to combine this all into ONE query so I dont have to use there and repeat my code?

[whoisgregg]

You can set multiple ORDER BY statements as shown below. They are interpreted from left to right, so you might need to play with different orders to get what you need.

// will first rank from highest to lowest price. Houses with identical prices will be further ranked by pending, then sold
$sql = "SELECT * FROM `listings` ORDER BY `price` DESC, `pending`, `sold` ASC";

You probably want something more like this:

// ranks first by sold or not, then pending or not, then within each of these possibilities will rank by price 
$sql = "SELECT * FROM `listings` ORDER BY `sold` DESC, `pending` DESC, `price` DESC";