Return order of MySQL SHOW COLUMNS?

To return order of MySQL SHOW COLUMNS, you need to use ORDER BY clause. The syntax is as follows −

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ‘yourTableName’ AND column_name LIKE 'yourStartColumnName%' ORDER BY column_name DESC;

Let us create a table in database TEST. The query to create a table is as follows −

mysql> create table OrderByColumnName -> ( -> StudentId int, -> StudentFirstName varchar(10), -> StudentLastName varchar(10), -> StudentAddress varchar(20), -> StudentAge int, -> StudentMarks int -> ); Query OK, 0 rows affected (1.81 sec)

Case 1 −

In this, the result is in descending order. Here is the query to return order of show column in MySQL −

mysql> SELECT COLUMN_NAME -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE table_name = 'OrderByColumnName' -> AND column_name LIKE 'student%' -> ORDER BY column_name DESC;

The following is the output −

+------------------+ | COLUMN_NAME | +------------------+ | StudentMarks | | StudentLastName | | StudentId | | StudentFirstName | | StudentAge | | StudentAddress | +------------------+ 6 rows in set (0.00 sec)

Case 2 − If you want the result in ascending order, there is no need to write ASC keyword because by default result will be in ascending order.

The query is as follows −

mysql> SELECT COLUMN_NAME -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE table_name = 'OrderByColumnName' -> AND column_name LIKE 'student%' -> ORDER BY column_name;

The following is the output −

+------------------+ | COLUMN_NAME | +------------------+ | StudentAddress | | StudentAge | | StudentFirstName | | StudentId | | StudentLastName | | StudentMarks | +------------------+ 6 rows in set (0.00 sec)