CODEWITHSUNDEEP
mysqlsql
Bintz
Bintz

Reputation: 811

MySQL order by value1 unless value1 < 0, then order by value2

I have a script that runs 2 rather slow SQL queries that are almost identical. One selects from the database records that are supposed to be "featured" on a page, i.e. they come first. Another selects from the database the regular records. Each query takes approximately 0.3 seconds, and since both run at the same time, I lose 0.6 seconds on them. So I was wondering if it would be possible to combine these queries. Here is what they look like:

SELECT * FROM records WHERE [other where conditions] AND featured>=$timestamp ORDER BY featured DESC

SELECT * FROM records WHERE [other where conditions] AND featured<$timestamp ORDER BY created DESC

What I have labeled [other where conditions] is identical in both cases. So what I essentially need to do is:

SELECT * FROM records WHERE [other where conditions] ORDER BY featured DESC UNLESS featured < $timestamp, created DESC

Except I don't know how to tell MySQL "ORDER BY ... UNLESS".

Upvotes: 1

Views: 404

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

You can do this with the order by alone:

SELECT *
FROM records
WHERE [other where conditions]
ORDER BY (case when features >= $timestamp then featured else $timestamp end) DESC,
         created desc;

Upvotes: 2

Maximilian Prepl
Maximilian Prepl

Reputation: 412

Yes, you can do this, code is something like this:

SELECT * FROM records 
    WHERE [other where conditions] 
ORDER BY 
    IF(featured < $timestamp, created, featured) DESC

And php version will be something like this:

$timestamp = now();
$SQLCond = "id = 4";
$SQLLimit = "LIMIT 0, 100";
$SQLMask = "SELECT * FROM records where " . $SQLCond . " ORDER BY IF(featured < " . mysql_real_escape_string($timestamp) . ", created, featured) DESC " . $SQLLimit;

Upvotes: 1

Michael Groh
Michael Groh

Reputation: 1

You could just create a featured flag in the rows returned like so:

SELECT CASE WHEN featured>=$timestamp THEN 1 ELSE 0 END AS `featuredRecord`, *
FROM records
WHERE [other where conditions]
ORDER BY featured DESC

The first record should be featured records and the rest are regular records.

Upvotes: 0

gen_Eric
gen_Eric

Reputation: 227290

You can use IF() for this.

SELECT * FROM records
WHERE [other where conditions]
ORDER BY IF(featured >= $timestamp, featured, created) DESC

Upvotes: 1

Related Questions