Order by a Field Being Equal to a Specific Value

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

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.

ORDER BY an equal value in MySQL

SELECT ...
FROM   ...
ORDER BY (meta_key='featured' AND meta_value='yes') DESC, postid ASC;

If (meta_key='featured' AND meta_value='yes') for a row, that row will have a 1/TRUE. Otherwise, it will have a 0/FALSE. Hence, sorting descending puts the rows that have TRUE first.

How to order by column if equal, order by another

SELECT
    *
FROM
    MyTable
ORDER BY 
    Col1,
    Col2 DESC

sql ORDER BY multiple values in specific order?

...
WHERE
   x_field IN ('f', 'p', 'i', 'a') ...
ORDER BY
   CASE x_field
      WHEN 'f' THEN 1
      WHEN 'p' THEN 2
      WHEN 'i' THEN 3
      WHEN 'a' THEN 4
      ELSE 5 --needed only is no IN clause above. eg when = 'b'
   END, id

How does order by clause works if two values are equal?

In relational databases, tables are sets and are unordered. The order by clause is used primarily for output purposes (and a few other cases such as a subquery containing rownum).

This is a good place to start. The SQL standard does not specify what has to happen when the keys on an order by are the same. And this is for good reason. Different techniques can be used for sorting. Some might be stable (preserving original order). Some methods might not be.

Focus on whether the same rows are in the sets, not their ordering. By the way, I would consider this an unfortunate example. The book should not have ambiguous sorts in its examples.

sql order by specific value in column and than order alphabetical the rest of the same column

I would try something like this:

DECLARE @myTable TABLE([Fruits] VARCHAR(20))
INSERT INTO @myTable VALUES('Apple')
INSERT INTO @myTable VALUES('Pear')
INSERT INTO @myTable VALUES('Peach')
INSERT INTO @myTable VALUES('Plum')
INSERT INTO @myTable VALUES('Grape')

SELECT * 
FROM @myTable
ORDER BY
    CASE WHEN([Fruits] = 'Grape') THEN 0 ELSE 1 END,
    [Fruits]

In the ORDER BY, you are assigning a value when the [Fruits] field is Grape so you can sort on that first, and then you do a secondary sort on the [Fruits] field.

What if the value of order field is the same for all the records

One simple answer is NO. There is no guarantee that the ORDER BY on equal values will return the same sorted result every time. It might seem to you it is always stable, however, there are many reasons when it could change.

For example, the sorting on equal values might defer after:

1. Gathering statistics
2. Adding an index on the column

For example,

Let's say I have a table t:

SQL> SELECT * FROM t ORDER BY b;

         A          B
---------- ----------
         1          1
         2          1
         3          2
         4          2
         5          3
         6          3

6 rows selected.

The sorting on the column having similar values is just like:

SQL> CREATE TABLE t1 AS SELECT * FROM t ORDER BY b, DBMS_RANDOM.VALUE;

Table created.

SQL> SELECT * FROM t1 ORDER BY b;

         A          B
---------- ----------
         1          1
         2          1
         4          2
         3          2
         5          3
         6          3

6 rows selected.

So, similar data in bot the tables, however, ORDER BY on the column having equal values, dos not guarantee the same sorting.

---

Related Topics