How to Use the SQL Order By Clause
What is the SQL Order By Clause?
When we work with databases using the SQL language, we often issue queries to retrieve data or information for any number of purposes and that data usually needs to be sorted or put into some kind of useful order. While results, by default, are returned in a way that might justifiably called chaotic; fortunately, there’s the SQL order by clause to eliminate this little frustration. You do need to know how to write at least a basic select statement; however, for a quick review, the simplest syntax is: SELECT * FROM nameOfTable. This simply selects all columns from a specified table. Seldom can your select statements be so simple and accomplish what you need to do; but, beginning practice with the SQL order by clause can and perhaps should be done with such simplicity so as to focus on it and not on other SQL commands. Notice also that I capitalize all SQL keywords to make them stand out in the midst of table and column names. This is not a requirement; but, it’s very good practice and helps to keep code clean which is a huge help when it comes time to work with it again to make major changes or if someone else has to take over your work.
When Should the SQL Order By Clause Be Used?
The answer to the question of when is it appropriate to use the SQL order by clause is determined by each individual situation. Consider the data you need to retrieve and then ask yourself if you need that data to be returned in not only an orderly fashion, but in a specific order of customized way. Remember, the SQL order by clause allows you to control query results by controlling the order in which data is presented. Think of some of the times when this might be necessary. As a web developer, I usually think in terms of database driven websites; but, you can also think of reports that one of your superiors in the workplace might need to study in order to make important decisions and to make evaluations of recently implemented changes or problems. They want sorted information, not data that is jumbled.
I will, however, use a web development scenario because that’s the area with which I’m most comfortable when it comes to working with the SQL language. Let’s look at two situations; one involves use of the SQL order by clause returning results in an order that is obvious to anyone looking at them; the other demonstrates use of the clause to return results in an order that isn’t readily detected by anyone but the one who wrote the clause. Keeping things simple, let’s say that you have a table with column names: f_name and l_name. Let’s name the table students. You could easily retrieve a list of all names with the command: SELECT * FROM students;. However, that statement wouldn’t do you much good if you needed the students’ names to be alphabetized.
You could write: SELECT * FROM students ORDER BY l_name ASC, f_name ASC;. The ASC indicates ascending order which would create a list of alphabetized last names with the last names beginning with A and ascending to Z. I selected all (the two) columns because not only would I want the last names alphabetized, I would also want two or more students with the same last name to appear in order by alphabetized first name. Because of this desire, notice that I ordered by last name first. Your list might look something like the following:
f_name | l_name
Karen | Albertson
Jason | Bates
Tim | Brown
Lisa | Smith
Tina | Smith
Ursula| Smith
Here’s an important note on the SQL order by clause; it does have a default order–ascending. This means that numbers increase going from small to large, text is ordered alphabetically, and dates are sorted from oldest to most recent. You may reverse this default by simply adding DESC immediately after the name of the column. Don’t worry, here’s my real life example (I changed my real table and column names): $q = “SELECT content_id, date_posted, image, title, summary, category FROM content, categories WHERE content.topic = categories.cat_id ORDER BY date_posted DESC LIMIT 3;”;. You can dismiss the period to end the sentence, the table and column names, and the WHERE and LIMIT statements.
Whether you understand them or not, the function of the SQL order by clause is the same. However, this time the results are not so obvious. Why? I coded to have the data appear in a table with titles within a database driven web page and only the most recent three pieces of content are to be displayed. Although no date is visible in the table (I don’t want it visible), the results are still ordered by the date on which the content was added. Please keep in mind that you can’t order by a field that doesn’t exist in your table. For example, when I created the table in this example, I created a date_posted field with a date type knowing ahead of time that I was going to use the SQL order by clause to control how content displayed by date. If this isn’t clear, you’ll want to first become very comfortable with creating and even populating tables before attempting to retrieve and sort data from them. Simply click on the screen shot below to see the results. Remember, practice makes perfect and it’s good to be as close to perfect as possible when it comes time to use the SQL order by clause or any other skill in the real world, especially if you must meet deadlines.
