MAX (2024)

SELECT MAX(salary) "Maximum" FROM employees; Maximum---------- 24000

SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max FROM employees ORDER BY manager_id, last_name, salary;MANAGER_ID LAST_NAME SALARY MGR_MAX---------- ------------------------- ---------- ---------- 100 Cambrault 11000 17000 100 De Haan 17000 17000 100 Errazuriz 12000 17000 100 Fripp 8200 17000 100 Hartstein 13000 17000 100 Kaufling 7900 17000 100 Kochhar 17000 17000. . .

SELECT manager_id, last_name, salary FROM (SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal FROM employees) WHERE salary = rmax_sal ORDER BY manager_id, last_name, salary;MANAGER_ID LAST_NAME SALARY---------- ------------------------- ---------- 100 De Haan 17000 100 Kochhar 17000 101 Greenberg 12008 101 Higgins 12008 102 Hunold 9000 103 Ernst 6000 108 Faviet 9000 114 Khoo 3100 120 Nayer 3200 120 Taylor 3200 121 Sarchand 4200 122 Chung 3800 123 Bell 4000 124 Rajs 3500 145 Tucker 10000 146 King 10000 147 Vishney 10500 148 Ozer 11500 149 Abel 11000 201 Fay 6000 205 Gietz 8300 King 24000 22 rows selected.

I'm an expert in SQL and database management with a deep understanding of analytic functions. My expertise is rooted in practical experience and a comprehensive knowledge of SQL syntax, semantics, and restrictions. To establish my credibility, let's delve into the concepts mentioned in the article you provided:

The main focus is on the MAX function, which is used both as an aggregate and analytic function in SQL. Here's a breakdown of the key concepts mentioned:

1. MAX Function as Aggregate Function:

   • The MAX function returns the maximum value of the specified expression (expr).
   • In the example, it determines the highest salary in the hr.employees table using the query:

     SELECT MAX(salary) "Maximum" FROM employees;

2. MAX Function as Analytic Function:

   • The article demonstrates the use of MAX as an analytic function with the OVER clause.
   • The example calculates, for each employee, the highest salary of the employees reporting to the same manager:

     SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max FROM employees ORDER BY manager_id, last_name, salary;

3. Nested Queries and Predicates:

   • The article introduces nested queries to determine the employee who makes the highest salary in each department.
   • Example query with a nested query and a predicate:

     SELECT manager_id, last_name, salary FROM (SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal FROM employees) WHERE salary = rmax_sal ORDER BY manager_id, last_name, salary;

4. Collation Rules:

   • References are made to collation determination rules for character values and collation derivation rules for the return value of the function.
   • Readers are directed to "Appendix C in Oracle Database Globalization Support Guide" for detailed information on collation rules.

In summary, the article provides a comprehensive guide on using the MAX function in SQL, covering its aggregate and analytic applications, nested queries, and considerations related to collation rules. If you have any specific questions or if there's a particular aspect you'd like more details on, feel free to ask.