Databases Reference
In-Depth Information
8. Execute a group-by query with a filter (using the
HAVING
clause):
SELECT CUST_CREDIT_LIMIT, MAX(CUST_YEAR_OF_BIRTH) AS DATAMAX
FROM CUSTOMERS
GROUP BY CUST_CREDIT_LIMIT
HAVING CUST_CREDIT_LIMIT > 10000
ORDER BY CUST_CREDIT_LIMIT;
9. Execute a group-by query with the same filter above applied in the
WHERE
clause:
SELECT CUST_CREDIT_LIMIT, MAX(CUST_YEAR_OF_BIRTH) AS DATAMAX
FROM CUSTOMERS
WHERE CUST_CREDIT_LIMIT > 10000
GROUP BY CUST_CREDIT_LIMIT
ORDER BY CUST_CREDIT_LIMIT;
10. Drop the index created in step 4:
SET AUTOT OFF
DROP INDEX IX_CUST_CREDIT_LIMIT;
How it works...
After connecting to the
SH
schema, we execute two queries to retrieve the
MIN
and
MAX
value
in the
CUST_CREDIT_LIMIT
field of the
CUSTOMERS
table. The execution plan for these two
queries is the same, and is as shown in the following screenshot:
We can see that there is a Full Table Scan of the
CUSTOMERS
table followed by a
SORT
AGGREGATE
to retrieve the
MIN
(or
MAX
) value as requested.
