Oracle найти максимальное значение
This article gives an overview of the MIN and MAX analytic functions. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Description
The Oracle/PLSQL MAX function returns the maximum value of an expression.
MIN and MAX as Aggregate Functions
The MIN and MAX aggregate functions are used to calculate the minimum and maximum values of a set of data respectively. As aggregate functions they reduce the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated values.
We can get more granularity of information by including a GROUP BY clause. In the following example we see the minimum and maximum values on a per-department basis.
In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.
Applies To
The MAX function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
MIN Analytic Function
The basic description for the MIN analytic function is shown below. The analytic clause is described in more detail here.
Using an empty OVER clause turns the MIN function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the minimum salary for all employees, as well as all the original data.
Adding the partitioning clause allows us to display the minimum salary per department, along with the employee data for each department.
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 could also use the SQL MAX function to return the name of each department and the maximum salary in the department.
Because you have listed one column in your SQL SELECT statement that is not encapsulated in the MAX function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
I know max is normally used for aggregates. What can I use here?
In the end, I want to use something like
where number_of_items is an integer and can be 0. I want to see total also in this case.
I'm not sure which version of SQL you're using, but I'd also like to throw this out there. SELECT total/IIF(number_of_items < 1, 1, number_of_items) from dual. I believe that should work.
Example - Using GROUP BY
In some cases, you will be required to use the GROUP BY clause with the MAX function.
For example, you could also use the MAX function to return the name of the department and the maximum salary in the 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.
Description
The SQL MAX function is used to return the maximum value of an expression in a SELECT statement.
MAX Analytic Function
The basic description for the MAX analytic function is shown below. The analytic clause is described in more detail here.
Using an empty OVER clause turns the MAX function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the maximum salary for all employees, as well as all the original data.
Adding the partitioning clause allows us to display the maximum salary per department, along with the employee data for each department.
Returns
The MAX function returns the maximum value.
Setup
The examples in this article require the following table.
Example - With Single Expression
The simplest way to use the SQL MAX function would be to return a single field that calculates the MAX value.
For example, you might wish to know the maximum salary of all employees.
In this SQL MAX function 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.
Parameters or Arguments
expression1, expression2, . expression_n Expressions that are not encapsulated within the MAX function and must be included in the GROUP BY clause at the end of the SQL statement. aggregate_expression This is the column or expression from which the maximum value will be returned. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. These are conditions that must be met for the records to be selected.
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, and 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:
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.
Answer: This is where things get a bit complicated. The SQL SELECT statement below will return the results that you want:
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:
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 with a 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.
This SQL SELECT 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 SELECT statement should work:
Question: I need help in a SQL query. I have a table in Oracle called cust_order which has the following fields: OrderNo, Customer_id, Order_Date, and Amount.
I would like to find the customer_id, who has Highest order count.
I tried with following query.
This gives me the max Count, But, I can't get the CUSTOMER_ID. Can you help me please?
Answer: The following SQL SELECT statement should return the customer with the highest order count in the cust_order table.
This SQL SELECT statement will summarize the total orders for each customer and then return the customer with the highest order count. This syntax is optimized for Oracle and may not work for other database technologies.
Question: I'm trying to get the employee with the maximum salary from department 30, but I need to display the employee's full information. I've tried the following query, but it returns the result from both department 30 and 80:
Answer: The SQL SELECT statement that you have written will first determine the maximum salary for department 30, but then you select all employees that have this salary. In your case, you must have 2 employees (one in department 30 and another in department 80) that have this same salary. You need to make sure that you are refining your query results to only return employees from department 30.
Try using this SQL SELECT statement:
This will return the employee information for only the employee in department 30 that has the highest salary.
This SQL tutorial explains how to use the SQL MAX function with syntax and examples.
Example - With Single Field
Let's look at some Oracle MAX function examples and explore how to use the MAX function in Oracle/PLSQL.
For example, you might wish to know how the maximum salary of all employees.
In this MAX function example, we've aliased the MAX(salary) expression as "Highest Salary". As a result, "Highest Salary" will display as the field name when the result set is returned.
Syntax
The syntax for the MAX function in Oracle/PLSQL is:
OR the syntax for the MAX function when grouping the results by one or more columns is:
35 Answers 35
I see many people use subqueries or else window functions to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS.
In other words: fetch the row from t1 where no other row exists with the same UserId and a greater Date.
(I put the identifier "Date" in delimiters because it's an SQL reserved word.)
In case if t1."Date" = t2."Date" , doubling appears. Usually tables has auto_inc(seq) key, e.g. id . To avoid doubling can be used follows:
Re comment from @Farhan:
Here's a more detailed explanation:
An outer join attempts to join t1 with t2 . By default, all results of t1 are returned, and if there is a match in t2 , it is also returned. If there is no match in t2 for a given row of t1 , then the query still returns the row of t1 , and uses NULL as a placeholder for all of t2 's columns. That's just how outer joins work in general.
The trick in this query is to design the join's matching condition such that t2 must match the same userid , and a greater date . The idea being if a row exists in t2 that has a greater date , then the row in t1 it's compared against can't be the greatest date for that userid . But if there is no match -- i.e. if no row exists in t2 with a greater date than the row in t1 -- we know that the row in t1 was the row with the greatest date for the given userid .
Wow Bill. This is the most creative solution to this problem I've seen. It is pretty performant too on my fairly large data set. This sure beats many of the other solutions I've seen or my own attempts at solving this quandary.
When applied to a table having 8.8 million rows, this query took almost twice as long as that in the accepted answer.
@Derek: Optimizations depend on the brand and version of RDBMS, as well as presence of appropriate indexes, data types, etc.
On MySQL, this kind of query appears to actually cause it to loop over the result of a Cartesian join between the tables, resulting in O(n^2) time. Using the subquery method instead reduced the query time from 2.0s to 0.003s. YMMV.
Is there a way to adapt this to match rows where date is the greatest date less than or equal to a user given date? For example if the user gives the date "23-OCT-2011", and the table includes rows for "24-OCT-2011", "22-OCT-2011", "20-OCT-2011", then I want to get "22-OCT-2011". Been scratching my head and reading this snippet for a while now.
This will retrieve all rows for which the my_date column value is equal to the maximum value of my_date for that userid. This may retrieve multiple rows for the userid where the maximum date is on multiple rows.
"Analytic functions rock"
Edit: With regard to the first comment .
"using analytic queries and a self-join defeats the purpose of analytic queries"
There is no self-join in this code. There is instead a predicate placed on the result of the inline view that contains the analytic function -- a very different matter, and completely standard practice.
"The default window in Oracle is from the first row in the partition to the current one"
The windowing clause is only applicable in the presence of the order by clause. With no order by clause, no windowing clause is applied by default and none can be explicitly specified.
When applied to a table having 8.8 million rows, this query took half the time of the queries in some the other highly voted answers.
Couldn't this return duplicates? Eg. if two rows have the same user_id and the same date (which happens to be the max).
Instead of MAX(. ) OVER (. ) you can also use ROW_NUMBER() OVER (. ) (for the top-n-per-group) or RANK() OVER (. ) (for the greatest-n-per-group).
In my tests using a table having a large number of rows, this solution took about twice as long as that in the accepted answer.
@user2067753 No, it doesn't return the full record. You can use the same MAX()..KEEP.. expression on multiple columns, so you can select all the columns you need. But it is inconvenient if you want a large number of columns and would prefer to use SELECT *.
I don't know your exact columns names, but it would be something like this:
Not at all. This will almost certainly be implemented as a full scan with a nested loop join to get the dates. You're talking about logical io's in the order of 4 times the number of rows in the table and be dreadful for non-trivial amounts of data.
FYI, "Not efficient, but works" is the same as "Works, but is not efficient". When did we give up on efficient as a design goal?
+1 because when your datatables are not millions of rows in length anwyays, this is the most easily understood solution. when you have multiple developers of all skill levels modifying the code, understandability is more important then a fraction of a second in performance that is unnoticable.
Not being at work, I don't have Oracle to hand, but I seem to recall that Oracle allows multiple columns to be matched in an IN clause, which should at least avoid the options that use a correlated subquery, which is seldom a good idea.
Something like this, perhaps (can't remember if the column list should be parenthesised or not):
EDIT: Just tried it for real:
So it works, although some of the new-fangly stuff mentioned elsewhere may be more performant.
This works nicely on PostgreSQL too. And I like the simplicity and generality of it -- the subquery says "Here's my criteria", the outer query says "And here's the details I want to see". +1.
I know you asked for Oracle, but in SQL 2005 we now use this:
I don't have Oracle to test it, but the most efficient solution is to use analytic queries. It should look something like this:
I suspect that you can get rid of the outer query and put distinct on the inner, but I'm not sure. In the meantime I know this one works.
Under the hood analytic queries sort the whole dataset, then process it sequentially. As you process it you partition the dataset according to certain criteria, and then for each row looks at some window (defaults to the first value in the partition to the current row - that default is also the most efficient) and can compute values using a number of analytic functions (the list of which is very similar to the aggregate functions).
In this case here is what the inner query does. The whole dataset is sorted by UserId then Date DESC. Then it processes it in one pass. For each row you return the UserId and the first Date seen for that UserId (since dates are sorted DESC, that's the max date). This gives you your answer with duplicated rows. Then the outer DISTINCT squashes duplicates.
This is not a particularly spectacular example of analytic queries. For a much bigger win consider taking a table of financial receipts and calculating for each user and receipt, a running total of what they paid. Analytic queries solve that efficiently. Other solutions are less efficient. Which is why they are part of the 2003 SQL standard. (Unfortunately Postgres doesn't have them yet. Grrr. )
This Oracle tutorial explains how to use the Oracle/PLSQL MAX function with syntax and examples.
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, and 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:
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.
Answer: This is where things get a bit complicated. The SQL SELECT statement below will return the results that you want:
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:
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 with a 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.
This SQL SELECT 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 SELECT statement should work:
Question: I need help in a SQL query. I have a table in Oracle called cust_order which has the following fields: OrderNo, Customer_id, Order_Date, and Amount.
I would like to find the customer_id, who has Highest order count.
I tried with following query.
This gives me the max Count, But, I can't get the CUSTOMER_ID. Can you help me please?
Answer: The following SQL SELECT statement should return the customer with the highest order count in the cust_order table.
This SQL SELECT statement will summarize the total orders for each customer and then return the customer with the highest order count. This syntax is optimized for Oracle and may not work for other database technologies.
Question: I'm trying to get the employee with the maximum salary from department 30, but I need to display the employee's full information. I've tried the following query, but it returns the result from both department 30 and 80:
Answer: The SQL SELECT statement that you have written will first determine the maximum salary for department 30, but then you select all employees that have this salary. In your case, you must have 2 employees (one in department 30 and another in department 80) that have this same salary. You need to make sure that you are refining your query results to only return employees from department 30.
Try using this SQL SELECT statement:
This will return the employee information for only the employee in department 30 that has the highest salary.
7 Answers 7
It looks like you're using Oracle so you can use the greatest function for this in place of max
As of Oracle 10.2 they introduced a GREATEST function which does what you want. There is also a LEAST function too.
select greatest(1,2) from dual;
select greatest(8,6,4,2) from dual;
select greatest(-1,-2) from dual;
select greatest('A','B','CCC','D') from dual;
You could use a CASE statement
should work here.
You'll have to create a new function for this:
Normally it would be:
Or (and this would probably be what you want in your case)
There may be a cleaner way to do it though.
UPDATE: Using your example of number_of_items and total from table XXX, it'd be:
UPDATE 2: Keep in mind, if you allow number of items to be 0, you will get an exception of division by 0. That's why in the other answer the user put a case and the else was the TOTAL, this way you don't get that exception.
It is possible to do this in Oracle 8.0 and older (i.e. before CASE was introduced) with the following mathematical trick:
. which is equivalent to max(1,number_of_items) .
Replace the three 1 s above with another value as required.
This works because number_of_items - 1 goes zero or negative when number_of_items is less than 1. And in general, x + abs(x) is always zero when x
We need this because some of our (3rd party) customers may still be using Oracle 8.0 and it would be many days of effort to find out if or when the last customer will finally upgrade!
Update: Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that row where that user has the latest date.
Syntax
The syntax for the MAX function in SQL is:
OR the syntax for the MAX function when grouping the results by one or more columns is:
Parameters or Arguments
expression1, expression2, . expression_n Expressions that are not encapsulated within the MAX function and must be included in the GROUP BY clause at the end of the SQL statement. aggregate_expression This is the column or expression from which the maximum value will be returned. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. These are conditions that must be met for the records to be selected.
Читайте также: