Using CREATE in SQL Mode › Writing a Sample SQL Query › Creating the Sample Query › Edit ORDER BY Clause

Edit ORDER BY Clause

An ORDER BY clause tells CA Dataquery the order in which data is to be presented in the query output. If an ORDER BY clause is not used the row order matches the order of rows in the result table. (The column order always matches the order of column names in the SELECT statement.) This section presents basic ORDER BY clause rules and uses and discusses creation of the ORDER BY clause of the sample query.

 .. ================================ T O P =====================================
 01 SELECT REP_ID, ORD_AMT, NAME
 02 FROM GLS_CUST_TABLE, GLS_ACCTS_TABLE
 03 WHERE GLS_CUST_TABLE.CUST_ID = GLS_ACCTS_TABLE.CUST_ID AND ORD_AMT > 1000.00
 04 ORDER BY column1
 .. ===========================  B O T T O M  ==================================

Facts

The ORDER BY clause lets you sort the result table rows into any order needed, based on the values of data found in specific columns. Name the columns whose contents are to be sorted and those columns determine the order in which rows are to be arranged on the report. For example, examine the following three groups of data.

Ordering the previous sample rows by NAME and ORD-ID produces the following results.

Ordering the previous sample rows by ORD_ID and NAME produces this result:

The following lists basic rules for using the keyword ORDER BY. Details, additional keywords, and formats are available in the CA Dataquery Reference Guide.

Location

An ORDER BY clause must be the last clause in a SELECT query.

Validity of ordering columns

You can use any column in any table referenced by the query as an ordering column, whether it is included in the SELECT clause or not.

Direction of sort

Rows are sorted in an ascending direction unless otherwise specified with a keyword of DESC for a descending sort.

Punctuation

Follow the words ORDER BY with a space and one or more column names. Use commas between column names.

Specify table ID

Where two or more tables are joined in the query and where a duplicate of an ORDER BY column exists, attach the table ID to the column name to indicate which column is to be used in ordering.

Example:

ORDER BY ACCT_TABLE.ORDER_DATE, CUST_ID

Steps

These are the steps for editing the ORDER BY clause to match the sample query:

Step 1

Move the cursor to the c in the word column1. Press the EOF or use the Delete key to delete the rest of the line.

Step 2

Type these characters beginning at the cursor location:

REP_ID, ORD_AMT, NAME

to tell CA Dataquery to arrange all the rows in the report into groups by REP_ID. Within each group of REP_IDs, the data is to be arranged in ascending order by the amount of the order (ORD_AMT). Where duplicate order amounts exist, the data is to be arranged in ascending order by customer name (NAME).

Results

The following is a sample of a portion of the panel showing how the SELECT statement looks now:

 DESCRIPTION:   SAMPLE BASIC QUERY__________________________________________
    ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+.
 .. ================================ T O P =====================================
 01 SELECT REP_ID, ORD_AMT, NAME
 02 FROM GLS_CUST_TABLE, GLS_ACCTS_TABLE
 03 WHERE GLS_CUST_TABLE.CUST_ID = GLS_ACCTS_TABLE.CUST_ID AND ORD_AMT > 1000.00
 04 ORDER BY REP_ID, ORD_AMT, NAME

Options

The following is a guide to other things you can do when you create your own queries.

Order report data by columns that are not printed.

You might like to group data on the report according to data in a column you are not interested in seeing printed. For instance, you might not want to see the same date printed on every line of output even though you might want the data ordered by date. You can use the date column in the ORDER BY clause while you omit it from the SELECT clause.

Change order of columns in SELECT clause.

You might want to print report columns in the same order as the columns in the ORDER BY clause.