barcode generator in vb.net code project Retrieving Data Using Data Query Language in Software

Encode PDF417 in Software Retrieving Data Using Data Query Language

Ascending sequence is the default for each column, but the keyword ASC may be added after the column name for ascending sequence, and DESC may be added for descending sequence. The column(s) named in the ORDER BY list do not have to be included in the query result set (that is, the SELECT list). However, it is better human engineering if you do. The SQL engine in the DBMS will gure out the best way to sequence the columns. In general, sorting data is an expensive process in terms of computer resources, so most DBMSs will use an index to access the rows in the desired sequence, assuming one exists, and only do an actual sort as a last resort. You can use column alias names in the ORDER BY clause, but when you do so, you are forcing the SQL engine in the DBMS to sort the result set after the query has been run. An index on the column(s) on which you are sorting the data cannot be used because that index references the column names and not the aliases. The point here is that the SQL engine has more exibility in how it goes about putting the result set in the requested sequence if you always use column names in the ORDER BY clause. Instead of column names, the relative position of the columns in the results may be listed. For example, ORDER BY 1,2 would order the results in ascending sequence by the rst two columns in the result set. The number provided has no correlation with the column position in the source table or view, however. This option is frowned upon in formal SQL because someone changing the query at a later time might shuf e columns around in the SELECT list and not realize that, in doing so, they are changing the columns used for sorting results. In the case of the prior SELECT statement, let s assume that presenting the rows in ascending order by MPAA Rating and Movie Genre Code would be useful. From a human engineering perspective, it s best to place those columns rst in the query results and in the same order as the ORDER BY column list (at least in languages that are read left to right). This makes the row ordering readily apparent to the reader. The revised SELECT statement and query results follow. All 20 rows in the MOVIE table are listed so you can see the sequencing. However, some movie titles were shortened so that each row ts on one line on the printed page. In typical SQL clients, rows that are too long wrap to a new line for display, but I have avoided that here because wrapped lines are so dif cult to read.

FROM MOVIE ORDER BY MPAA_RATING_CODE, MOVIE_GENRE_CODE; RATING -----PG-13 PG-13 PG-13 PG-13 PG-13 PG-13 PG-13 PG-13 PG-13 PG-13 PG-13 R R R R R R R R R GENRE ----ActAd ActAd ActAd ActAd Comdy Comdy Comdy Comdy Drama Rmce Rmce ActAd ActAd ActAd Drama Drama Drama Drama Drama Forgn MOVIE_TITLE -------------------------------------------The Italian Job Pirates of the Caribbean: The Curse of the Master and Commander: The Far Side of the The Day After Tomorrow 50 First Dates Matchstick Men The School of Rock Something's Gotta Give Big Fish Two Weeks Notice 13 Going on 30 Man on Fire Kill Bill: Vol. 1 The Last Samurai Mystic River Lost in Translation Road to Perdition Cold Mountain Monster Das Boot

(20 row(s) affected)

Note that in the result set, all rows for the same value of MPAA Rating Code (RATING column) appear together, and within each MPAA Rating the rows are ordered by the Movie Genre Code (GENRE column). Most IT (Information Technology) professionals would say that the result set is ordered by Genre within Rating. Just for fun, let s order the rows by Genre descending within Rating ascending. Here is the revised SELECT statement and the corresponding result set (from an Oracle database this time, using the SQL*Plus client, so you can see the differences in the formatting of the result set):

SELECT MPAA_RATING_CODE AS RATING, MOVIE_GENRE_CODE AS GENRE, MOVIE_TITLE FROM MOVIE ORDER BY MPAA_RATING_CODE ASC, MOVIE_GENRE_CODE DESC