SQL VISUAL QUICKSTART GUIDE- P13:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Chapter 4 Sorting Rows with ORDER BY You can sort by the results of expressions Chapter 5 describes how to create expressions by using functions and operators Listing and Figure . You can intermingle column names relative column positions and expressions in ORDER BY. You should create indexes for columns that you sort frequently see Chapter 12 . The sequence in which unordered rows appear actually is based on the physical order of rows in the DBMS table. You shouldn t rely on physical order because it changes often such as when rows are added updated or deleted or an index is created. Sorting by relative column position is useful in UNION queries see Combining Rows with UNION in Chapter 9. DBMS DBMSs restrict the columns that can appear in an ORDER BY clause depending on data type. For example in Microsoft SQL Server you can t sort by ntext text and image columns and in Oracle you can t sort by blob clob nclob and bfile columns. Search your DBMS documentation for SELECT or ORDER BY. In Microsoft Access you can t use an expression s column alias in ORDER BY. To run Listing either retype the expression in the ORDER BY clause ORDER BY price sales DESC or use the relative column position ORDER BY 4 DESC Listing This query sorts by an expression. See Figure for the result. I ve created a column alias for the expression because it would be cumbersome to repeat the expression in the ORDER BY clause and because it creates a more meaningful column label in the result. Listing SELECT title_id price sales price sales AS Revenue FROM titles ORDER BY Revenue DESC title_id price sales Revenue T07 1500200 T05 201440 T12 100001 T03 25667 T11 94123 T13 10467 T06 11320 T02 9566 T04 13001 T09 5000 T08 4095 T01 566 T10 NULL NULL NULL Figure Result of Listing . .
