CODEWITHSUNDEEP
phpmysqlsql-order-by
user317005
user317005

Reputation:

Order records by timestamp, but with specified ID listed first

MySQL

+----+------------+
| id | timestamp  |
+----+------------+
| 1  | 1306922400 |
+----+------------+
| 2  | 1306926000 |
+----+------------+
| 3  | 1306929600 |
+----+------------+

PHP

$a = mysql_query("SELECT * FROM `table` ORDER BY ?? DESC, `timestamp` DESC");

Order by:

  1. Start where id is equal to 2

  2. Then order by timestamp

Order should be: 2, 3, 1

Upvotes: 1

Views: 788

Answers (4)

Jody
Jody

Reputation: 8291

SELECT * FROM `table` where id = 2
union 
SELECT * FROM `table` where id != 2 order by id, timestamp desc

Upvotes: 1

a1ex07
a1ex07

Reputation: 37382

SELECT * FROM `table` ORDER BY `id`!=2 ASC,  `timestamp` DESC
// or
SELECT * FROM `table` ORDER BY `id`=2 DESC,  `timestamp` DESC

Upvotes: 0

dynamic
dynamic

Reputation: 48141

you could use UNION... But I would avoid it.

$a = mysql_query("
                  (SELECT * FROM `table` WHERE id = 2 ) UNION 
                  (SELECT * FROM table ORDER BY timestamp DESC)
                ");

Considering i didn't an UNION ALL in the second query the record with id = 2 will get not duplicated.

Upvotes: 1

Psyborg
Psyborg

Reputation: 11

$a = mysql_query("SELECT * FROM `table` WHERE id = 2 ORDER BY `timestamp` DESC UNION SELECT * FROM `table` WHERE id != 2 ORDER BY `timestamp DESC");

Upvotes: -1

Related Questions