Custom ORDER BY logic in an SQL query

Question

I'm looking for an elegant solution to the following problem:

I currently have a page listing entries ORDER BY date, with a pagination LIMIT. Here's a snippet of my code:

<?php
$intPageNumber = 1;
if ( isset($this->GET['p']) && (integer)$this->GET['p'] > 1 ) $intPageNumber = (integer)$this->GET['p'];
$strLimit = " LIMIT " . (($intPageNumber-1)*$intItemsPerPage) . ", " . $intItemsPerPage;

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM jos_h_testimonials ORDER BY date DESC ".$strLimit;
// code to load some records here
// ...

// take care of pagination
$db->setQuery('SELECT FOUND_ROWS()');
$intTotalRecords = $db->LoadResult();
$this->NumberOfPages = ceil( $intTotalRecords / $intItemsPerPage);
$this->CurrentPage = $intPageNumber;
?>

Table schema:

CREATE TABLE IF NOT EXISTS `jos_h_testimonials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `author` varchar(255) NOT NULL,
  `testimonial` text NOT NULL,
  `rating` int(11) NOT NULL,
  `date` int(11) NOT NULL,
  `hgroup_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

This is quite a standard way of handling listing entries in a paged fashion. My problem is that the client wants to special ordering - they wish to have entries with a certain value for the hgroup_id foreign key to appear at the top.

The two solutions I can think of are:

1. Load the entire list and re-order it using PHP, then handle pagination using PHP;
2. Run two queries - one to select entries with a desired foreign key, the second one to select all other entires. Merge result sets and.. handle pagination using PHP.

Either way, my elegant single-query SQL_CALC_FOUND_ROWS approach to pagination won't work. I was curious if there's some MySQL "magic" that I could employ in the ORDER_BY clause?

I hope the above makes sense..

Answer 1

Use simple SQL:

SELECT SQL_CALC_FOUND_ROWS *
FROM jos_h_testimonials
ORDER BY
    -- Special case on top
    CASE WHEN hgroup_id = 9 THEN 0
    -- All the rest
    ELSE 1 END ASC,
    -- Regular sort order
    date DESC