Retrieving Information from a Table › Putting Rows in Order

Putting Rows in Order

Use ORDER BY

To sort selected rows by the values in a column, use the ORDER BY clause. CA OLQ assumes you want rows in ascending order. If you want rows in descending order, specify DESCENDING as Example 2 shows below.

CA OLQ sorts selected rows by the first column named in the ORDER BY clause. It then sorts each group of rows sharing a common value in order of the second column named in the ORDER BY clause, and so on. For example, you might want to display a table of bank transactions in order of branch number, and within each branch number, in order of transaction date.

Example 1— Sorting on 1 column

For each employee in department 4000, list the ID, name, and hire date starting with the first person hired to the last person hired:

select empid, lastname, firstname, startdate  as &xq.hire date'
  from emp
  where deptid = 4000
  order by startdate ! display

                                   EMP REPORT
                                    mm/dd/yy

         EMPID           LASTNAME            FIRSTNAME         HIRE DATE
         ─────        ───────────────        ──────────        ─────────
          0476        ZEDI                   BETSY                760223
          0158        JACKSON                JOCK                 770707
          0149        PENMAN                 LAURA                770908
          0007        BANK                   MONTE                780430
          0120        ANGELO                 MICHAEL              790908
          0127        MCDOUGALL              CAROL                800607
     END OF REPORT

Example 2— Sorting on 2 columns

List the department ID, employee name, and hire date of all employees sorted by department. Within each department, list the employees in alphabetic descending order:

select deptid, lastname, startdate as &xq.hire date'
  from emp
  order by deptid, lastname descending  ! display

                                   EMP REPORT
                                    mm/dd/yy

             DEPTID               LASTNAME                HIRE DATE
             ──────            ───────────────            ─────────
               3100            TYRO                          801221
               3100            LITERATA                      800909
               3100            JENSEN                        820929
               3100            JACOBI                        811111
               3100            HEAROWITZ                     810909
               3100            GRANGER                       800527
               3100            GARFIELD                      770121
               3100            GALLWAY                       811010
               3100            DOUGH                         760808
               4000            ZEDI                          760223
               4000            PENMAN                        770908
               4000            MCDOUGALL                     800607
               4000            JACKSON                       770707
               4000            BANK                          780430

                                      - 1 -

Sorting on calculated columns

If you want to sort the contents of your report based on values in a calculated column, specify the column number in the ORDER BY clause. Count the columns from left to right, beginning with 1.

Example

List salaries and anticipated 5% year-end bonus for employees in department 4000. List rows in order of smallest to largest bonus:

select lastname, salary, &xq.year end bonus=', salary*0.005
  from emp
  where deptid=4000
  order by 4 ! display

                                   EMP REPORT
                                    mm/dd/yy

        LASTNAME          SALARY

     ANGELO                18000.00    YEAR END BONUS=             90.00000
     MCDOUGALL             18000.00    YEAR END BONUS=             90.00000
     JACKSON               34000.00    YEAR END BONUS=            170.00000
     ZEDI                  37000.00    YEAR END BONUS=            185.00000
     PENMAN                39000.00    YEAR END BONUS=            195.00000
     BANK                  80000.00    YEAR END BONUS=            400.00000
     END OF REPORT