Sql как найти все максимальные

At first glance…

All you need is a GROUP BY clause with the MAX aggregate function:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

It’s never that simple, is it?

I just noticed you need the content column as well.

This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job’s technical interview.

It is, actually, so common that Stack Overflow community has created a single tag just to deal with questions like that: greatest-n-per-group.

Basically, you have two approaches to solve that problem:

Joining with simple group-identifier, max-value-in-group Sub-query

In this approach, you first find the group-identifier, max-value-in-group (already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier and max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Left Joining with self, tweaking join conditions and filters

In this approach, you left join the table with itself. Equality goes in the group-identifier. Then, 2 smart moves:

1. The second join condition is having left side value less than right value
2. When you do step 1, the row(s) that actually have the max value will have NULL in the right side (it’s a LEFT JOIN, remember?). Then, we filter the joined result, showing only the rows where the right side is NULL.

So you end up with:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Conclusion

Both approaches bring the exact same result.

If you have two rows with max-value-in-group for group-identifier, both rows will be in the result in both approaches.

Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its “flavor”.

Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.

Есть таблица с данными:

| user_id | price |
--------------------
| 1        |   30      |
--------------------
| 1        |   20      |
--------------------
| 1        |   50      |
--------------------
| 1        |   70      |
--------------------
| 2        |   30      |
--------------------
| 2        |   30      |
--------------------
| 3        |   30      |
--------------------
| 3        |   5       |
--------------------

Хочу найти user_id у которого сумма столбцов price будет максимальной. Ответ будет:

| user_id | price |
--------------------
| 1        |   170      |
--------------------

Для достижения резульатат делаю запрос:

SELECT user_id, SUM(price) FROM `order` GROUP BY user_id ORDER BY SUM(price) DESC LIMIT 1;

Но может быть ситуация, что будет два user_id с максимальными суммами и суммы будут одинаковые:

| user_id | price |
--------------------
| 1        |   170      |
--------------------
| 4        |   170      |
--------------------

Как тогда будет выглядеть запрос? Типа такого:

SELECT o.user_id, MAX(found_sum) FROM (SELECT user_id, SUM(price) AS found_sum FROM 'order' GROUP BY user_id) AS o;

Here are three examples that use SQL to find and select the row with the maximum value in a given column.

The examples work in most major RDBMSs, including MySQL, MariaDB, PostgreSQL, SQLite, Oracle, and SQL Server.

Sample Data

We’ll start with the following data:

SELECT * FROM PetShow;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
| 2       | Scratch   | 3       |
| 3       | Tweet     | 65      |
| 4       | Bark      | 8       |
| 5       | Ruff      | 15      |
| 6       | Woof      | 20      |
| 7       | Punch     | 3       |
+---------+-----------+---------+

Option 1

Here’s an example of selecting the row with the maximum value from the Score column in the above table:

SELECT 
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MAX(Score) FROM PetShow );

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
+---------+-----------+---------+

We used the MAX() function within a subquery to find the maximum value, and returned the whole row with the outer query.

When there are Multiple Rows with the Max Value

Using this method, if there’s more than one row with the max value, all of them are returned.

Suppose we insert another row into our table with the same score as the existing max score:

INSERT INTO PetShow VALUES (8, 'Purr', 85);
SELECT * FROM PetShow;

Our table now looks like this:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
| 2       | Scratch   | 3       |
| 3       | Tweet     | 65      |
| 4       | Bark      | 8       |
| 5       | Ruff      | 15      |
| 6       | Woof      | 20      |
| 7       | Punch     | 3       |
| 8       | Purr      | 85      |
+---------+-----------+---------+

We can see that both Wag and Purr have got the highest score of 85.

Let’s run the previous query again to return the maximum value from that column:

SELECT 
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MAX(Score) FROM PetShow );

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
| 8       | Purr      | 85      |
+---------+-----------+---------+

Both rows with the max values are returned as expected.

We can limit the result set to just one row if required. The exact code will depend on the RDBMS being used.

The LIMIT clause can be used with RDBSs such as PostgreSQL, MariaDB, MySQL, and SQLite:

SELECT 
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MAX(Score) FROM PetShow )
ORDER BY PetId ASC
LIMIT 1;

Result:

+-------+---------+-------+
| PetId | PetName | Score |
+-------+---------+-------+
|     1 | Wag     |    85 |
+-------+---------+-------+

In SQL Server, we can use the TOP clause:

SELECT TOP 1
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MAX(Score) FROM PetShow )
ORDER BY PetId ASC;

Result:

+-------+---------+-------+
| PetId | PetName | Score |
+-------+---------+-------+
|     1 | Wag     |    85 |
+-------+---------+-------+

And in Oracle Database:

SELECT
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MAX(Score) FROM PetShow )
ORDER BY PetId ASC
FETCH FIRST 1 ROW ONLY;

Result:

+-------+---------+-------+
| PetId | PetName | Score |
+-------+---------+-------+
|     1 | Wag     |    85 |
+-------+---------+-------+

Option 2

If we only want one row returned, we can actually do away with most of the other code and just get the first row out of the ordered results:

SELECT 
    PetId,
    PetName,
    Score
FROM PetShow 
ORDER BY Score DESC
LIMIT 1;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
+---------+-----------+---------+

In SQL Server:

SELECT TOP 1
    PetId,
    PetName,
    Score
FROM PetShow 
ORDER BY Score DESC;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
+---------+-----------+---------+

And in Oracle Database:

SELECT
    PetId,
    PetName,
    Score
FROM PetShow 
ORDER BY Score DESC
FETCH FIRST 1 ROW ONLY;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
+---------+-----------+---------+

Option 3

Another way to select the row with the maximum value is to join the table on itself, like this:

SELECT 
    p1.PetId, 
    p1.PetName, 
    p1.Score
FROM PetShow p1
LEFT JOIN PetShow p2 ON p1.Score < p2.Score
WHERE p2.PetId IS NULL;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
| 8       | Purr      | 85      |
+---------+-----------+---------+

As with the earlier example, we can limit the results to one row (or some other number) if required.

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

Description

The SQL MAX function is used to return the maximum value 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 function when grouping the results by one or more 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 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.

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.

SELECT MAX(salary) AS "Highest salary"
FROM 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.

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.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY 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.

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:

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 SELECT 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 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.

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 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:

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

---

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.

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

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.

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 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:

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

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:

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

This will return the employee information for only the employee in department 30 that has the highest salary.