ColdFusion 4.0 Primer
7. Sorting Results
When you run a select statement the results will be returned in the order they were entered into the table. This isn't always what you want, sometimes you may want to order the results by a set of criteria. To do this you use the Order by clause.
Select Firstname, Lastname From Friends Order by Lastname
What the above will do is return a list of your friends name's ordered by their last name alphabetically. If you wanted to start with the Z's and work your way to the A's you would change it to read Order by Lastname DESC. DESC is short for descending and will reverse the order in which the records are listed.
But what if you want to order by more than one column? Lets assume you have a friend named John Smith and a friend named Peter Smith. They will be listed in the right place by their last name but whether or not John is above Peter is dependent on the order you entered them into the database.
To fix this you simply pass two column names in the Order clause.
Select Firstname, Lastname From Friends Order by Lastname, Firstname
8. Limiting Results
Often times it is desirable to only display 10 records at a time, for instance most search engines work this way. To do this you add a few arguments to your query tag.
<CFQUERY DATASOURCE = "Friends" Name = "Query1" Maxrows = "10" Startrow = "0"> .. .. .. </CFQUERY>
Note that the starting row is set at 0. Remember that computers start numbering at 0, not 1. So the first record in the database is record number 0.
Conclusion
If you've made it this far then you should be able to make a database driven website using ColdFusion quite easily. I have covered what is needed to achieve the most commonly desired results of a database driven website, but I have only scratched the surface. There is a lot more to ColdFusion and relational databases that I couldn't cover in a short tutorial. If you would like to learn more I suggest ColdFusion Web Application Construction Kit 4.0 by Ben Forta. It is without a doubt the most comprehensive book I own and it should be able to assist you in all your ColdFusion endeavors.
Site Categories
Recent Blog Posts
