Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Interactive Paradox Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Local SQL Part 6
Controlling Sort Order
© 2001 Lawrence G. DiGiovanni
Digico, Inc.
www.digicoinc.com


Preface

Local SQL is a very powerful yet commonly underutilized tool that is provided with Paradox (as part of the Borland Database Engine). It exposes the power and flexibility of the SQL sublanguage to both Paradox developers as well as interactive end-users.

This is the sixth in a planned series of articles on using Local SQL with Paradox/BDE applications. This article discusses how to affect the sort order of a result set generated by a SQL SELECT statement, using the ORDER BY clause.


Introduction

Often when performing queries, you want to be able to specify the sort order of the result set (the ANSWER table) at the time of the query. SQL provides a means to accomplish this via the ORDER BY clause of the SQL SELECT statement. The ORDER BY clause, when present, is the last clause of the SQL SELECT statement.

To use the ORDER BY clause, provide a comma-delimited list of column names. These column names must also be present in the SELECT clause. SQL uses this list to determine the sort order to use when producing the result set. The result set is sorted by the columns listed in the order by clause, in the order they are listed, left to right. Default sort order is ascending (ASC), but you can specify DESC with any column name in the ORDER BY clause to indicate a descending sort order in the result set.

You can also use column aliases or specify the ordinal position of a column in the SELECT clause.

As with the last article, our examples will use the DBDEMOS database, which contains the Biolife example database.


ORDER BY Example

Let’s take a simple example from an earlier article.
SELECT company, city, state
  FROM customer
Produces a list of company names, in no particular order. Let’s say we want to sort the result set by Company name. We would modify the above query like so:
SELECT company, city, state
  FROM customer
ORDER BY company
The addition of ‘ORDER BY Company’ causes the result set to be ordered by Company name. We can reverse the sort order (which we recall from earlier is ascending), by adding the DESC modifier to the ORDER BY clause.
SELECT company, city, state
  FROM customer
ORDER BY company DESC
We could alternately use the ordinal position of the Company column in the select list:
SELECT company, city, state
  FROM customer
ORDER BY 1
Since company is the first column of the select list, we can indicate to order by this column by its ordinal position. This is convenient when dealing with long column names or complex expressions, but in complex queries can obfuscate the query to an observer.

Now let’s say we wanted to sort the above listing by state, then by city, then by company:
SELECT company, city, state
  FROM customer
ORDER BY state, city, company
Again, this can also be expressed:
SELECT company, city, state
  FROM customer
ORDER BY 3, 2, 1

Ordering By Expressions

Let’s consider another earlier example:
SELECT Description, (ListPrice - Cost)/Cost
  FROM parts
This query shows the percent markup for each item. If we wanted to see the result set sorted by increasing markup, we could add an ORDER BY clause:
SELECT Description, (ListPrice - Cost)/Cost
  FROM parts
ORDER BY 2
We could also use a column alias to specify the sort order
SELECT Description, (ListPrice - Cost)/Cost AS PctMkup
  FROM parts
ORDER BY PctMkup
The use of an aliased column name in an ORDER BY clause is preferable to ordinal position for the simple fact that if you change column order in the SELECT list, this would impact your sort order if you use positional notation.


Summary

The SQL ORDER BY clause provides a means to specify the sort order of a result set (ANSWER table). The ORDER BY clause is a comma separated list of column names or ordinal numbers denoting the position of a column in the SELECT list. The ASC and DESC modifiers affect the direction of the sort of a given column, though ASC is the default and is not typically used. When it is desired to sort a result set of a calculated column, the ordinal number or a column alias may be used.


Local SQL Part 7


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.