Explanation of Sorting Columns Retrieved from Database Table in SQL - Succeed in Using SQL. Also refer to Access, MySQL, MS SQL Server, ID numbers, ORDER BY, ascending, descending, multiple columns, Ron Kurtus, School for Champions. Copyright © Restrictions
Sorting Columns Retrieved from a Database Table in SQL
by Ron Kurtus (3 February 2007)
When you retrieve a column from a database table using SQL, the data is usually listed in order of ID numbers, which may not be handy to use. You can sort the records in the column, so that they will be displayed in a specific order. To do that, you use the ORDER BY clause, which is the last clause in your SELECT statement. You can sort in ascending order, descending order and according to multiple columns in your table.
Questions you may have include:
- How do you sort in ascending order?
- How do you sort in descending order?
- How do you sort with multiple tables?
This lesson will answer those questions. There is a mini-quiz near the end of the lesson.
Ascending order
To display the data in a column in an alphanumeric order, you used the ORDER BY clause and repeat the name of the column.
SELECT column_name
FROM table_name
ORDER BY column_name;
Descending order
You can a sort in a descending order by including the DESC cause at the end of your ORDER BY clause.
SELECT column_name
FROM table_name
ORDER BY column_name DESC;
Multiple columns
There are a number of combinations of sorting when making a query of multiply columns.
Sort by data in specific column
If you are retrieving data from several columns, you can still sort by the data in a specific column. For example, sorting table_name by the record names in column_2, in descending order, you would write:
SELECT column_1, column_2, column_7
FROM table_name
ORDER BY column_2 DESC;
Sort by one column and then the other
If you want to sort the data by one column and then the other, you state the column names after the ORDER BY clause, in the order of preference and separated by a comma.
SELECT column_1, column_2, column_7
FROM table_name
ORDER BY column_2, column_1;
Note that the sorting is done in ascending order in this example.
Sort ascending and descending
You can also mix ascending and descending orders in your sorting.
SELECT column_1, column_2, column_7
FROM table_name
ORDER BY column_2 DESC, column_1;
The sorting is done first in descending order of column_2, and then those items are sorted in ascending order in column_1.
Summary
You can sort the records in the column, so that they will be displayed in a specific order by using the ORDER BY clause. You can sort in ascending order, descending order and according to multiple columns in your table.
Always do your best
Resources
The following are resources on this subject.
Websites
Books
Mini-quiz to check your understanding
1. Why can't you use the ORDER BY clause before other clauses?
2. What order should you put the tables after the ORDER BY clause?
3. Can you mix ascending and descending sorts in a query?
If you got all three correct, you are on your way to becoming a Champion in working with SQL. If you had problems, you had better look over the material again.
What do you think?
Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. We will try to get back to you as soon as possible.
Share link
Feel free to establish a link from your website to pages in this site.
Or use our form to send this link to yourself or a friend.
Students and researchers:
The Web address of this page is
www.school-for-champions.com/sql/sorting_columns.htm.
Please include it as a reference in your report, document, or thesis.
Where can you go from here?
Sorting Columns Retrieved from a Database Table in SQL