Brought To You By
Tomlin Ventures Ltd


Using the SQL ORDER BY statement

The ORDER BY clause is used to specify the return order of a query string. Put simply it is a way of choosing one or more columns by which the return data will be sorted. It can be used with most SELECT statements and generally appears as one of the last clauses in a query string.

ORDER BY can have two possible values.

Ascending meaning to start with the lowest and increase.
Descending meaning to start with the largest and decrease.

An example query using ORDER BY would look something like this.

SELECT * FROM table_name ORDER BY column_name DESC

This would return any rows sorted by the column_name in descending order.

The ORDER BY clause will sort both numerical values and textual values. In the case of text the letter A is considered low and the letter Z is high. Special Characters occur after the Z character.

Important! Text and numerical columns

When sorting data from a column that is set to a text value it is important to understand that the ordering is done by a single character at a time. This is simple enough when dealing with text. It means however that when ordering numbers within a text column those numbers are not treated as numerical indices.

For example in a text column a value of 2 will appear after a value of 100 in an ascending order query as only the first characters 2 and 1 will be directly compared. To get these values to appear in a purely numerical order they would need to be shown as 002 and 100.

To sort numbers as actual numerical values they must appear within a column that is set to take numerical values only.

ORDER BY multiple columns

Ordering by more than one column is also possible by stating the relevant columns as shown.

SELECT * FROM table_name ORDER BY col_one, col_two DESC

In this case the return data will be ordered by col_one and then where there are repeated values from col_one they will then be ordered by the values in col_two.

In the following example the column named grade is a text column, as such the numerical values are treated as text as explained above.

ORDER BY Example

The below example shows how you would order data extracted from a table named grade by multiple columns. It also shows how different data types are delt with during the ORDER BY process

id name grade
1 ben A
2 bob B
3 ann A
4 amy B
5 tim 100
6 dan A
7 lea 50
8 zoe *

So a valid ordered query would be.

SELECT name, grade FROM grade_table ORDER BY grade, name ASC

And the results would look like this.

name grade
tim 100
lea 50
ann A
ben A
dan A
amy B
bob B
zoe *

You can see from these results how the ORDER BY clause deals with different data types. It is often a good idea to limit your ORDER BY statements to columns which contain only one data type.

Proceed to the next article below to see how we can add more clauses to our queries to effect their returned results.

Share to Facebook Share to Twitter Digg This Stumble It Share to Reddit Share to Delicious Technorati LinkedIn Google Buzz Blogger More...

blog comments powered by Disqus