In this video, you will see how to sort the result set of a SELECT statement.
After completing this lesson, you will be able to:
In this video, you will see how to sort the result set of a SELECT statement.
SELECT FROM /dmo/flight
FIELDS carrier_id,
connection_id,
flight_date,
seats_max - seats_occupied AS seats
WHERE carrier_id = 'AA'
AND plane_type_id = 'A320-200'
* ORDER BY seats_max - seats_occupied DESCENDING,
* flight_date ASCENDING
INTO TABLE @DATA(result).
out->write(
EXPORTING
data = result
name = 'RESULT'
).
CTRL + F3
to activate the class and F9
to execute it as a console app.ORDER BY
addition. Change the sorting direction, sort the result by other fields or expressions to get familiar with the syntax.Addition DISTINCT allows you to remove duplicates from the result set of a SELECT statement. The addition has to be placed right before the first element of the field list.
Watch this video to know how.
SELECT FROM /dmo/connection
FIELDS
* DISTINCT
airport_from_id,
distance_unit
ORDER BY airport_from_id
INTO TABLE @DATA(result).
out->write(
EXPORTING
data = result
name = 'RESULT'
).
CTRL + F3
to activate the class and F9
to execute it as a console app.An aggregate expression calculates a single value from an sql_expression (sql_exp) by calling an aggregate function for multiple rows of a results set.
If the field list of a SELECT statement contains nothing but aggregate functions, the result set always consists of exactly one row. Note that this is also the case if the aggregation is based on an empty data set.
In our example, the SELECT statement on the left illustrates the data set on which the aggregations in the SELECT on right are based.
You can see that the maximum in column DISTANCE is 6217 while the minimum is 6162. The total number of rows is 4 and column AIRPORT_FROM_ID contains 3 distinct values (value 'FRA' appears twice).
SELECT FROM /dmo/connection
FIELDS carrier_id,
connection_id,
airport_from_id,
distance
WHERE carrier_id = 'LH'
INTO TABLE @DATA(result_raw).
out->write(
EXPORTING
data = result_raw
name = 'RESULT_RAW'
).
*********************************************************************
SELECT FROM /dmo/connection
FIELDS MAX( distance ) AS max,
MIN( distance ) AS min,
SUM( distance ) AS sum,
AVG( distance ) AS average,
COUNT( * ) AS count,
COUNT( DISTINCT airport_from_id ) AS count_dist
WHERE carrier_id = 'LH'
INTO TABLE @DATA(result_aggregate).
out->write(
EXPORTING
data = result_aggregate
name = 'RESULT_AGGREGATED'
).
CTRL + F3
to activate the class and F9
to execute it as a console app.If the field list of a SELECT statement contains a mixture of aggregate functions and other elements, for example, columns, expressions, and so on, the syntax of SQL requires a GROUP BY clause. The GROUP BY clause has to list all columns that appear in the field list outside of aggregations.
In the SELECT statement on the right, two columns are used in the field list: CARRIER_ID and DISTANCE. Column DISTANCE only appears as input for aggregate functions and therefore is not required after GROUP BY. But column CARRIER_ID is used outside of the aggregate functions and therefore has to be listed after GROUP BY.
The effect of GROUP BY can be see in the result of the SELECT statement. The data set, on which the aggregation is performed, is separated into subsets (groups) that have the same values in the GROUP BY - fields. The final result set contains one row for each group. The aggregate expressions are evaluated for each group separately.
For example, the row with CARRIER_ID = 'AA' contains COUNT = 5 and MAX = 4156 because the subset with CARRIER_ID = 'AA' contains 5 rows and within this subset the maximum value for distance is 4156.
SELECT FROM /dmo/connection
FIELDS
* carrier_id,
MAX( distance ) AS max,
MIN( distance ) AS min,
SUM( distance ) AS sum,
COUNT( * ) AS count
* GROUP BY carrier_id
INTO TABLE @DATA(result).
out->write(
EXPORTING
data = result
name = 'RESULT'
).
CTRL + F3
to activate the class and F9
to execute it as a console app.FIELDS
(the one with field name carrier_id). Notice the syntax error that tells you that field CARRIER_ID is missing in the GROUP BY
clause.GROUP BY
addition and activate again.Log in to track your progress & complete quizzes