SQL max () function usage

Simplest syntax

Select max (expression)
FROM tables
WHERE predicates;

Instance

The code is as follows:	Copy code
Select max (salary) as "Highest salary" FROM employees;

Make it use together with GROUP

The code is as follows:	Copy code
SELECT department, MAX (salary) as "Highest salary" FROM employees Group by department;

Q: I am trying to pull out some information about a table. For simplicity, assume that the table (report_history) has four columns:

The code is as follows:	Copy code
USER_NAME, report_job_id, REPORT_NAME, report_run_date.

Each report is written to this table during Oracle runtime, and the preceding information is noted. What I'm trying to do is pull from this table when every different run report and who run it last time.

My queries:

The code is as follows:	Copy code
SELECT report_name, max (report_run_date) FROM report_history Group by report_name

However, this result is incorrect. Let's take a look at the correct method.

The code is as follows:	Copy code
SELECT rh. user_name, rh. report_name, rh. report_run_date FROM report_history rh, (SELECT max (report_run_date) as maxdate, report_name FROM report_history Group by report_name) maxresults WHERE rh. report_name = maxresults. report_name AND rh. report_run_date = maxresults. maxdate; Let's take a few moments to explain what we 've ve done.

Method 2

The code is as follows:	Copy code
(SELECT max (report_run_date) as maxdate, report_name FROM report_history Group by report_name) maxresults