Basic about MAX Function – using and example

This SQL tutorial explains how to use the SQL MAX feature with syntax and examples.

Description

The SQL MAX characteristic is used to return the maximum cost of an expression in a SELECT statement.

Syntax

The syntax for the MAX function in SQL is:

SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the MAX characteristic when grouping the outcomes by using one or greater columns is:

SELECT expression1, expression2, ... expression_n,
       MAX(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, … expression_n Expressions that are now not encapsulated within the MAX feature and need to be covered in the GROUP BY clause at the cease of the SQL statement. aggregate_expression This is the column or expression from which the most price will be returned. tables The tables that you wish to retrieve records from. There ought to be at least one table listed in the FROM clause. WHERE conditions Optional. These are conditions that must be met for the information to be selected.

Example – With Single Expression

The simplest way to use the SQL MAX characteristic would be to return a single subject that calculates the MAX value.

For example, you might desire to understand the maximum earnings of all employees.

SELECT MAX(salary) AS "Highest salary"
FROM employees;

In this SQL MAX feature example, we’ve aliased the MAX(salary) area as “Highest salary”. As a result, “Highest salary” will show as the field title when the end result set is returned.

Example – Using SQL GROUP BY Clause

In some cases, you will be required to use the SQL GROUP BY clause with the SQL MAX function.

For example, you ought to additionally use the SQL MAX function to return the identify of every branch and the maximum earnings in the department.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;

Because you have listed one column in your SQL SELECT announcement that is now not encapsulated in the MAX function, you should use the SQL GROUP BY clause. The department discipline must, therefore, be listed in the GROUP BY section.

Frequently Asked Questions

Question: I’m attempting 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, and report_run_date.

Each time a file is run in Oracle, a record is written to this table noting the above info. What I am making an attempt to do is pull from this table when the final time each awesome record was once 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 now not supply the name of the user who ran the report.

Adding user_name to each the pick listing and to the group through clause returns more than one traces for each report; the outcomes exhibit the remaining time each person ran every record in question. (i.e. User1 ran Report 1 on 01-JUL-03, User2 ran Report1 on 01-AUG-03). I do not desire that….I just want to be aware of who ran a particular report the last time it was run.

Any suggestions?

Answer: This is the place things get a bit complicated. The SQL SELECT announcement below will return the outcomes 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 provide an explanation for what we’ve done.

First, we’ve aliased the first occasion of the report_history desk as rh.

Second, we have covered two aspects in our FROM clause. The first is the desk known as report_history (aliased as rh). The 2d is a pick 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 have aliased the complete end result set as maxresults.

Now, that we’ve got created this choose statement inside our FROM clause, Oracle will let us be a part of these outcomes towards our unique report_history table. So we have joined the report_name and report_run_date fields between the tables referred to as rh and maxresults. This allows us to retrieve the report_name, max(report_run_date) as properly as the user_name.

Question: I want assist with a SQL query. I have a table in Oracle known as orders which has the following fields: order_no, customer, and amount.

I need a query that will return the purchaser who has ordered the best complete amount.

Answer: The following SQL return the consumer with the very best whole quantity 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 SELECT statement will summarize the complete orders for each customer and then return the purchaser with the easiest complete orders. This syntax is optimized for Oracle and may additionally now not work for different database technologies.

Question: I’m attempting to retrieve some information from an Oracle database. I’ve got a desk named Scoring with two fields – Name and Score. What I desire to get is the absolute best score from the table and the identify of the player.

Answer: The following SQL SELECT assertion must work:

SELECT Name, Score
FROM Scoring
WHERE Score = (SELECT MAX(Score) FROM Scoring);

Question: I want help in a SQL query. I have a table in Oracle referred to as cust_order which has the following fields: OrderNo, Customer_id, Order_Date, and Amount.

I would like to locate the customer_id, who has Highest order count.

I tried with following query.

SELECT MAX(COUNT(*))
FROM CUST_ORDER
GROUP BY CUSTOMER_ID;

This offers me the max Count, But, I can’t get the CUSTOMER_ID. Can you assist me please?

Answer: The following SQL SELECT statement have to return the patron with the best order count number in the cust_order table.

SELECT query1.*
FROM (SELECT Customer_id, Count(*) AS order_count
      FROM cust_order
      GROUP BY cust_order.Customer_id) query1,

     (SELECT max(query2.order_count) AS highest_count
      FROM (SELECT Customer_id, Count(*) AS order_count
            FROM cust_order
            GROUP BY cust_order.Customer_id) query2) query3
WHERE query1.order_count = query3.highest_count;

This SQL SELECT assertion will summarize the complete orders for every consumer and then return the client with the very best order count. This syntax is optimized for Oracle and can also not work for different database technologies.

Question: I’m attempting to get the worker with the most revenue from department 30, however I want to display the employee’s full information. I’ve tried the following query, but it returns the end result from both department 30 and 80:

SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary)
                FROM employees
                WHERE department_id=30);

Answer: The SQL SELECT announcement that you have written will first determine the most salary for department 30, however then you select all employees that have this salary. In your case, you must have two personnel (one in department 30 and another in department 80) that have this same salary. You want to make certain that you are refining your query effects to only return employees from department 30.

Try using this SQL SELECT statement:

SELECT *
FROM employees
WHERE department_id=30
AND salary = (SELECT MAX(salary)
              FROM employees
              WHERE department_id=30);

This will return the worker statistics for solely the worker in department 30 that has the absolute best salary.