![]() |
![]() |
|
![]() |
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 customerProduces 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 companyThe 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 DESCWe could alternately use the ordinal position of the Company column in the select list: SELECT company, city, state FROM customer ORDER BY 1Since 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, companyAgain, 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 partsThis 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 2We could also use a column alias to specify the sort order SELECT Description, (ListPrice - Cost)/Cost AS PctMkup FROM parts ORDER BY PctMkupThe 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. ![]() |
![]() |
|