SQL: MAX Function
The MAX function returns the maximum value of an expression.
The syntax for the MAX function is:
SELECT MAX(expression )
FROM tables
WHERE
predicates;
Simple Example
For example, you might wish to know the maximum salary of all employees.
SELECT MAX(salary) as "Highest salary"
FROM employees;
In this example, we've aliased the max(salary) field as "Highest salary". As
a result, "Highest salary" will display as the field name when the result set is
returned.
Example using GROUP BY
In some cases, you will be required to use a GROUP BY clause with the MAX
function.
For example, you could also use the MAX function to return the name of each
department and the maximum salary in the department.
SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP
BY department;
Because you have listed one column in your SELECT statement that is not
encapsulated in the MAX function, you must use a GROUP BY clause. The department
field must, therefore, be listed in the GROUP BY section.
Frequently Asked Questions
Question: I'm trying to pull some info out of a table. To
simplify, let's say the table (report_history) has 4 columns:
user_name, report_job_id, report_name, report_run_date.
Each time a report is run in Oracle, a record is written to this table noting
the above info. What I am trying to do is pull from this table when the last
time each distinct report was run and who ran it last.
My initial query:
SELECT report_name, max(report_run_date)
FROM report_history
GROUP BY
report_name
runs fine. However, it does not provide the name of the user who ran the
report.
Adding user_name to both the select list and to the group by clause returns
multiple lines for each report; the results show the last time each person ran
each report in question. (i.e. User1 ran Report 1 on 01-JUL-03, User2 ran
Report1 on 01-AUG-03). I don't want that....I just want to know who ran a
particular report the last time it was run.
Any suggestions?
Answer: This is where things get a bit complicated. The SQL
statement below will return the results that you want:
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 done.
First, we've aliased the first instance of the report_history table as
rh.
Second, we've included two components in our FROM clause. The first is the
table called report_history (aliased as rh). The second is a select
statement:
(SELECT max(report_run_date) as maxdate,
report_name
FROM
report_history
GROUP BY report_name)
maxresults
We've aliased the max(report_run_date) as maxdate and we've aliased
the entire result set as maxresults.
Now, that we've created this select statement within our FROM clause, Oracle
will let us join these results against our original report_history table. So
we've joined the report_name and report_run_date fields between the tables
called rh and maxresults. This allows us to retrieve the
report_name, max(report_run_date) as well as the user_name.
Question: I need help in an SQL query. I have a table in Oracle
called orders which has the following fields: order_no, customer, and
amount.
I need a query that will return the customer who has ordered the highest
total amount.
Answer: The following SQL should return the customer with the
highest total amount in the orders table.
select query1.* from
(SELECT customer, Sum(orders.amount) AS
total_amt
FROM orders
GROUP BY orders.customer)
query1,
(select max(query2.total_amt) as highest_amt
from (SELECT customer, Sum(orders.amount) AS total_amt
FROM
orders
GROUP BY orders.customer) query2) query3
where
query1.total_amt = query3.highest_amt;
This SQL statement will summarize the total orders for each customer and then
return the customer with the highest total orders. This syntax is optimized for
Oracle and may not work for other database technologies.
Question: I'm trying to retrieve some info from an Oracle
database. I've got a table named Scoring with two fields - Name and
Score. What I want to get is the highest score from the table and the name of
the player.
Answer: The following SQL should work:
SELECT Name, Score
FROM Scoring
WHERE Score = (select Max(Score) from
Scoring);
It
accounts for 34% of all lost-workday injury and illness — and costs almost $20
billion annually, according to the 
스크랩
프린트
스탠스 방향을 바꿀 때는 볼과의 거리를 일정하게
유지한다.
CHECK
경사지에서 스윙한다는 것은 평탄한 페어웨이에 비해 자세가 무너지기 쉬운 상황이다. 약간만이라도 크게 움직이면
낮은 방향을 향해 중심이 흔들리고 자세가 무너진다. 그러므로 이미 설명한 대로 어떤 경사에도 결코 무리하지 않고, 콤팩트한 스윙을 하도록
한다.
