SAP CDS Views: How to Sort Results with ORDER BY Clause

In SAP ABAP CDS Views, you can apply sorting to the data retrieved by the view using the ORDER BY clause. This allows you to order the result set based on one or more fields, in either ascending or descending order.

Syntax for Sorting in CDS Views

The syntax for sorting in CDS views is quite simple. You use the ORDER BY clause after the SELECT statement to define which fields should be used for sorting and the order direction (ascending or descending).

Basic Example of Sorting in CDS Views

@AbapCatalog.sqlViewName: 'ZFLIGHT_SORT'
@EndUserText.label: 'Flight Data Sorted by Date and Price'
define view ZFLIGHT_SORT as select from sflight {
    key carrid,
    key connid,
    fldate,
    price
}
order by fldate asc, price desc

Explanation:

• order by fldate asc, price desc: This will order the results first by fldate (ascending order) and then by price (descending order).
• asc: Stands for ascending order (lowest to highest).
• desc: Stands for descending order (highest to lowest).

Sorting with Multiple Columns

You can sort the data based on multiple columns. If you specify more than one column in the ORDER BY clause, the data is sorted by the first column. If there are ties (i.e., multiple rows have the same value for the first column), the second column will be used to break the tie, and so on.

Example: Sorting by Multiple Columns

@AbapCatalog.sqlViewName: 'ZFLIGHT_SORT_MULTI'
@EndUserText.label: 'Flight Data Sorted by Carrier and Price'
define view ZFLIGHT_SORT_MULTI as select from sflight {
    key carrid,
    key connid,
    fldate,
    price
}
order by carrid asc, price desc

Explanation:

• The result set is first sorted by carrid in ascending order.
• If there are rows with the same carrid, the sorting continues by price in descending order.

Sorting with Aliased Fields

If you are using calculated fields or aliased fields in your CDS view, you can also sort the data by these fields.

Example: Sorting with Aliased Fields

@AbapCatalog.sqlViewName: 'ZFLIGHT_SORT_ALIAS'
@EndUserText.label: 'Sorted Flight Data with Aliased Field'
define view ZFLIGHT_SORT_ALIAS as select from sflight {
    key carrid,
    key connid,
    fldate,
    price,
    price * 1.1 as price_with_tax
}
order by price_with_tax desc

Explanation:

• In this example, we calculate a new field price_with_tax (by multiplying the price by 1.1), and we sort the result set by this new calculated field (price_with_tax) in descending order.

Performance Considerations

Sorting in CDS views can have performance implications, especially when dealing with large datasets. When designing your CDS view, you should keep the following in mind:

• Indexes: Ensure that the fields you are sorting by are indexed in the underlying database table, if possible. This can speed up the sorting operation.
• Avoid Sorting Large Datasets: If possible, limit the number of rows that need to be sorted by applying appropriate filters (e.g., using parameters or WHERE clauses) before sorting the data.

Conclusion

• ORDER BY is used in CDS views to sort the result set based on one or more columns.
• Sorting can be applied to one or multiple columns with options for ascending (asc) or descending (desc) order.
• You can sort by calculated fields or aliased fields in CDS views.
• Always consider the impact on performance, especially with large datasets, and ensure indexing on the columns used for sorting.

By using the ORDER BY clause effectively, you can ensure that your CDS view returns data in the correct order for your specific use case.