MySQL ORDER BY Clause

MySQL SELECT

Using MySQL ORDER BY Clause you can sort table columns by ascending or descending.

Suppose we have a table ‘tbl’ with column ‘name’ and a row like below values:

1
1B
10
2
10C
2A
10Z

So, when you sort by name:

SELECT name
FROM tbl
ORDER BY name;

So, when you sort by that column you will get the result:

1
10
10C
10Z
1B
2
2A

This is not like that what we expected. This ia natural sorting.
So we need to CAST this to get our desired result like below:

SELECT name
FROM tbl
ORDER BY CAST(name AS UNSIGNED);

Result:
1
1B
2
2A
10
10C
10Z

ORDER BY FIELD:

Sometimes a specific order is required in a query which cannot be done using either ASC or DESC or using a special sort field.
Using FIELD( ) function in the ORDER BY clause you can achieve this.

SELECT name
FROM tbl
ORDER BY FIELD(name, '1B', '2A') desc;

Result:
2A
1B
1
10
2
10C
10Z

For any types of queries, you can contact us on info@namasteui.com.

You may like:  Manage Triggers in MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *