CODEWITHSUNDEEP
phpmysql
cronoklee
cronoklee

Reputation: 6712

mySQL custom sort order ORDER BY FIELD() issues

I'm trying to sort the resutls of a SELECT statement using a custom order like so:

SELECT * FROM table ORDER BY FIELD(id,4,5,6) LIMIT 6

I was expecting to have returned rows with ids: 4,5,6,1,2,3 but instead I'm getting 1,2,3,7,8,9. What am I doing wrong?

As a side note: Prior to running this query, I'm pulling this sort order from the database using a different SELECT with a GROUP_CONCAT function like so:

SELECT group_concat(clickID ORDER BY count DESC separator ',') from table2 WHERE searchphrase='$searchphrase'

This results in the 4,5,6 which is then used in the main query. Is there a faster way to write this all in one statement?

Upvotes: 1

Views: 205

Answers (3)

Barmar
Barmar

Reputation: 781038

here's how to do it all in one query

SELECT DISTINCT t1.*
FROM table t1
LEFT JOIN table2 ON t1.id = t2.clickID AND t2.searchphrase='$searchphrase'
ORDER BY t2.clickID IS NULL ASC, t1.id ASC

When the LEFT JOIN finds no match, it sets the fields in t2 to NULL in the returned row. This orders by this nullness.

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

There is no need of the FIELD function. That will only make things slow.

You just need to properly use the ORDER BY:

SELECT * FROM table
ORDER BY id IN (4,5,6) DESC, id
LIMIT 6

Upvotes: 1

peterm
peterm

Reputation: 92785

Try it this way

SELECT *
  FROM table1
 ORDER BY FIELD(id, 4,5,6) > 0 DESC, id
  LIMIT 6

Output:

| ID |
|----|
|  4 |
|  5 |
|  6 |
|  1 |
|  2 |
|  3 |

Here is SQLFiddle demo

Upvotes: 3

Related Questions