Php MySQL Results Order By
Hello Guys Welcome to Coding Gurus Online Portal ...!
In MySQL, there are various clauses like WHERE, GROUP BY, ORDER BY. From them, we have used WHERE clause with the examples of MySQL JOINs for applying conditions to merge two tables using MySQL join query. In this article, we are going to use ORDER BY clause for queries. Before that, some important points about this MySQL clause are listed below.
- 1. ORDER BY can only be applied for MySQL select queries.
- 2. This clause is used to sort database table records, with the select query specification given.
- 3. If we use this clause for a query, then it should be followed by the column name on which sorting will be applied, and again, followed by order specification(ASC/DESC) in which the results will be expected.
- 4. Possible keywords used for mentioning order following the column name is ASC and DESC, whereas, the default is ASC that would be taken, if there is no specified order following the column name.
- 5. ORDER BY clause will sort either alphabetically or numerically, based on the type of values, the columns are containing.
- 6. In a MYSQL SELECT query, the keywords are used with the order specified in the following query syntax.
SELECT column_name1,column_name1,... FROM table_name WHERE condition ORDER BY column_name
- 7. We can sort database table records about more than one column, by specifying such comma separated column names, following ORDER BY keyword.
- 8. To see the effect on sorting about multiple columns, the leading column should not contain distinct values; Rather, the secondly specified column will be taken to sort, if and only if the first column has duplicate values.
Example: Sorting MySQL Table Records using ORDER BY
With the enough basic information about MySQL ORDER BY keyword, we can go with a simple example for getting sorted results using MySQL SELECT query.
Let us have the students table that we have taken as an example for explaining MySQL left join and right join, some days before. This table structure is once again shown below for reference.
This table contains list of students records where each has the name of the student, email and applied date.
ORDER BY Single Column
Now we are going to sort these records based on the name of the students in descending order. For that, the MySQL SELECT query can be generated like as follows,
SELECT * FROM students ORDER BY student_name DESC
If we want to display the query results in ascending order, then we need not specify the keyword ASC. Rather, it is taken by default by simply specifying,
In this query we are not using WHERE clause which should come before ORDER BY keyword if we have to apply any condition on selecting records.
On executing this query, the students table records will be displayed in descending alphabetical order of the student_name column as specified in the query. And the query results are shown below.
ORDER BY More than One Column
As we have seen that the ORDER BY keyword will be used for sorting records about more than one column, here, we are going to generate a query to select list of student table records sorted in descending order of its applied_date and student_name columns.
SELECT * FROM students ORDER BY applied_date DESC, student_name DESC
This query will sort students records in descending order. If any number if these records has same value for applied_date, then, these records will be sorted with the student_name column in descending order. Now, the resultant structure on executing the above query is,