Ordering by Specific Field Value First

Ordering by specific field value first

There's also the MySQL FIELD function.

If you want complete sorting for all possible values:

SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core", "board", "other")

If you only care that "core" is first and the other values don't matter:

SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC

If you want to sort by "core" first, and the other fields in normal sort order:

SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC, priority

There are some caveats here, though:

First, I'm pretty sure this is mysql-only functionality - the question is tagged mysql, but you never know.

Second, pay attention to how FIELD() works: it returns the one-based index of the value - in the case of FIELD(priority, "core"), it'll return 1 if "core" is the value. If the value of the field is not in the list, it returns zero. This is why DESC is necessary unless you specify all possible values.

SQL Order By: Specific Value First, then Ordering?

Use a case expression to put Needs Response rows first. Then order by date descending:

order by case when status = 'Needs Response' then 0 else 1 end, date desc

ORDER BY specific values first in SQL Server

You can use CASE in the ORDER BY clause.

ORDER BY CASE WHEN ID = 4 THEN 0 ELSE 1 END ASC, 
         NAME ASC

MySQL - order by field, starting after specific value

If you ORDER BY score, id, then you should be able to use filtering instead.

SELECT * 
FROM test 
WHERE (score = lastScoredDisplayed AND id > lastIdDisplayed) -- Continue with last score
   OR score > lastScoreDisplayed -- continue on to next score
ORDER BY score, id
LIMIT 5;

Where lastScoreDisplayed and lastIdDisplayed are the values from the last row of your previous query.

This kind of technique can even be faster than the LIMIT x, x technique; LIMIT x, x has to sort the entire dataset every time, whereas this technique ends up with a smaller set the further you go.

Ordering by specific field value LIKE first

We can use expressions in the ORDER BY clause, for example:

 SELECT t.description
   FROM table1 t
  ORDER
     BY t.description LIKE 'Wo%' DESC 
      , t.description

The expression t.description LIKE 'Wo%' is going to evaluate to 0, 1 or NULL.
So NULL values of description will sort last, values that satisfy the condition will sort first (because we've specified DESC for descending sort order.

The more portable, more ANSI-standards compliant equivalent would be:

 SELECT t.description
   FROM table1 t
  ORDER
     BY CASE WHEN t.description LIKE 'Wo%' THEN 1 
             WHEN t.description NOT LIKE 'Wo%' THEN 0 
        END DESC
      , t.description

---

If we could omit that second WHEN and just do ELSE 0, which would give us slightly different behavior with NULL values of descrption.

If we don't like the naked "foo LIKE bar" expression in the ORDER BY clause, we could wrap it in a MySQL function, to make it more clear to the future reader,

 SELECT t.description
   FROM table1 t
  ORDER
     BY IF(t.description LIKE 'Wo%',1,0) DESC
      , t.description

---

Again, the key point here is that we can use expressions in the ORDER BY clause. For testing, we can include that same expression in the SELECT list, so we can "see" (in the returned results) what those expressions are evaluating to. But note there isn't a requirement that the expressions in the ORDER BY have to appear in the SELECT list.

Symfony2 Orderby specific field value first

The CASE construction is vendor specific and not part of Doctrine 2 by default. However you can have a look into the DoctrineExtensions. You can use the IfElse function for your use case.

Yii2 OrderBy specific field value first

Use yii\db\Expression :

$orderBy = (new \yii\db\Query())
         ->select('*')
         ->from('product')
         ->orderBy([new \yii\db\Expression('FIELD (id, 3,1,2,4)')])
         ->all();

Ordering SQL query by specific field values

Order By Case Edition
    When 'NE' Then 1
    When 'OE' Then 2
    When 'OP' Then 3
    Else 4 End 

Oracle - Order by specific values first, then the rest

This works for all DB engines

SELECT ITEM, STATUS 
FROM FOO 
ORDER BY case when STATUS = 'I' then 1
              when STATUS = 'U' then 2
              when STATUS = 'P' then 3
              else 4
         end,
         status

---

Related Topics