MySQL Sort Specific Value First – ORDER BY FIELD

In the world of relational databases, the ability to sort data is fundamental to organizing and presenting information in a meaningful way. However, there are scenarios where a standard ascending or descending order doesn’t quite meet the requirements. One common need is to sort data with specific values first, followed by the rest of the dataset. In this article, we will explore how to achieve this using the SQL ORDER BY clause.

In this comprehensive guide, we will explore three techniques for sorting by specified values in SQL:

• Using the CASE statement
• Simple equality conditions
• The FIELD() function.

Let’s create a sample table named DemoTable and insert some data into it. Then, we’ll run the SQL query to demonstrate sorting with specific values first.

-- Create the DemoTable
CREATE TABLE DemoTable (
    id INT,
    name VARCHAR(50)
);

-- Insert sample data into DemoTable
INSERT INTO DemoTable (id, name) VALUES
    (1, 'John'),
    (2, 'Sam'),
    (3, 'Alice'),
    (4, 'Ros'),
    (5, 'Bob');

Now, we have a table DemoTable with the following data:

+----+-------+
| id | name  |
+----+-------+
| 1  | John  |
| 2  | Sam   |
| 3  | Alice |
| 4  | Ros   |
| 5  | Bob   |
+----+-------+

1. Using CASE Statement

Let’s use the CASE statement to sort the data with ‘Sam’ first:

SELECT * FROM DemoTable
ORDER BY 
    CASE 
        WHEN name = 'Sam' THEN 1
        ELSE 2
    END, name ASC;

Breaking it down:

Primary Sorting Condition (CASE WHEN name = 'Sam' THEN 1 ELSE 2 END):

• The CASE statement assigns a value of 1 to rows where name = 'Sam' and 2 to all other rows.
• The primary sorting is based on these assigned values in ascending order.

Secondary Sorting Condition (name ASC):

• If two or more rows have the same value assigned by the CASE statement the name column is used as a secondary sorting condition.
• Within each group of rows with the same assigned value, the rows are sorted alphabetically based on the name column in ascending order.

The result will be:

+------+-------+
| id   | name  |
+------+-------+
|    2 | Sam   |
|    3 | Alice |
|    5 | Bob   |
|    1 | John  |
|    4 | Ros   |
+------+-------+

2. Using Simple Equality Condition

Now, let’s explore sorting by a specified value using a simple equality condition:

SELECT * FROM DemoTable
ORDER BY (name = 'Sam') DESC, name ASC;

Primary Sorting Condition ((name = 'Sam') DESC):

• The (name = 'Sam') expression evaluates to true (1) for rows where name = 'Sam' and false (0) for others. Rows where name = 'Sam' will be placed at the top of the result set due to the DESC (descending) order.
• Rows where the condition is not true (i.e., name != 'Sam') will be placed after the rows where name = 'Sam'.

Secondary Sorting Condition (name ASC):

• If two or more rows have the same value assigned by expression evaluation, the name column is used as a secondary sorting condition.
• Within each group of rows with the same assigned value, the rows are sorted alphabetically based on the name column in ascending order.

3. Using FIELD() Function

Let’s now use the FIELD() function to sort data based on specific values:

SELECT * FROM DemoTable
ORDER BY FIELD(name, 'Sam') DESC, name ASC;

Primary Sorting Condition (FIELD(name, 'Sam') DESC):

• The FIELD(name, 'Sam') function returns 1 for rows where name = 'Sam' and 0 for others.
• The primary sorting is based on these values in descending order (DESC), placing rows with name = 'Sam' at the top.

Secondary Sorting Condition (name ASC):

• If two or more rows have the same value assigned by the FIELD() function, the name column is used as a secondary sorting condition.
• Within each group of rows with the same assigned value, the rows are sorted alphabetically based on the name column in ascending order.

Conclusion

Sorting data by specified values in SQL can be achieved using various techniques, each suited to different scenarios. Whether using the CASE statement, simple equality conditions, or the FIELD() function, these approaches provide flexibility in customizing the sorting order based on specific criteria. The choice between these techniques depends on the complexity of the sorting requirements and the desired level of customization in your SQL queries.

See more:

• How to check Max and Existing Connections in MySQL
• Difference between CREATE INDEX and ALTER TABLE ADD INDEX in MySQL