Using ORDER BY twice in a query not working?

[imgrooot]

I have a basic query where I am retrieving project records. I basically want to show the records from newest to oldest, but I also want to show the Featured projects first. Each Featured project is marked by "1" in the mysql table column.  If it's not featured, then it's marked "0".

$find_records = $db->prepare("SELECT * FROM projects ORDER BY project_id DESC, featured DESC LIMIT 10");
$find_records->execute();
$result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  foreach($result_records as $row) {
    
  }
}

The issue with above query is that the ORDER BY works only for the first component(project_id) and ignores the second component(featured). Do you see what's wrong with it?

[cyberRobot]

As it's written, the query results will be sorted by "project_id" in reverse (descending) order. The "featured" part of the clause will only get executed if you have multiple records with the same project_id.

If you want the featured records to always appear first, you'll need to sort the results by that column first, then by project ID.

[Psycho]

To add some clarification, when there are multiple ORDER BY conditions, the query will order by the first condition. Then, if there are records with the same value in that first condition, then it will sort those records by the second condition. And so on through all conditions. I.e. the second (subsequent) condition(s) only apply when two records have the same value for the first (preceding) conditions.

As @cyberRobot stated, your query will first sort all records by the project_id. Then, if any records have the same project_id (a scenarios I expect would never occur), then it would sort those records would be sorted by the featured value.

I would also add that I assume project_id is an auto-increment primary key field. Using that for sorting by newest/oldest will "probably" work in most instances, but is a poor implementation. There are many scenarios where such a field would not necessarily be in order of when the records were created. I would suggest having a "date_created" field that is auto-populated when new records are created and using that for sorting by newest/oldest

Edited February 3, 2020 by Psycho

[imgrooot]

That makes sense. I reversed the order and it seems to work fine now.

Yes project_id is an auto-increment primary key field. I honestly never had issues with this method before so I kept using it. But I actually do have "date_created" with each record. So I can "ORDER BY date_created" if need be.

[Psycho]

32 minutes ago, imgrooot said:

That makes sense. I reversed the order and it seems to work fine now.

Yes project_id is an auto-increment primary key field. I honestly never had issues with this method before so I kept using it. But I actually do have "date_created" with each record. So I can "ORDER BY date_created" if need be.

Not so much about "need be" as it is you "should be". As I said, an auto-increment field will probably work in most situations, but if you have a date field you should absolutely be using that. It's about using good programming techniques. For example, how records are created/managed could change (especially in large projects with many developers). There could be logic that allows a record to be "replaced" with a new record that simply changes the existing record and sets a new "created date". Sorting by the ID would then have that record display out of sequence. And understand that is just an example, there could be any number of reasons why the sequence of the id would not be the creation order - either due to business rules or other bad practices.

[imgrooot]

2 hours ago, Psycho said:

Not so much about "need be" as it is you "should be". As I said, an auto-increment field will probably work in most situations, but if you have a date field you should absolutely be using that. It's about using good programming techniques. For example, how records are created/managed could change (especially in large projects with many developers). There could be logic that allows a record to be "replaced" with a new record that simply changes the existing record and sets a new "created date". Sorting by the ID would then have that record display out of sequence. And understand that is just an example, there could be any number of reasons why the sequence of the id would not be the creation order - either due to business rules or other bad practices.

Good to know. I will do that from now on.