От автора: этим уроком мы откроем новую тему для нашего сайта — цикл уроков, в которых мы будем учиться писать SQL запросы и решать распространенные повседневные задачи, с которыми вы можете столкнуться при работе с MySQL. Из этого урока вы узнаете, как получить максимальное значение.
С задачей, которую мы поставили для текущего урока — как получить максимальное значение — мы можем сталкиваться часто. Например, у нас есть интернет магазин и необходимо получить самый дорогой товар каталога и всю информацию о нем. Или необходимо получить, скажем, ТОП-10 самых дорогих товаров. Или выбрать наиболее дорогие товары определенной категории. Или, наоборот, получить минимальные значения и всю информацию, связанную с ними.
Согласитесь, это тот круг задач и вопросов, которые действительно могут встречаться сплошь и рядом. Для данного урока мы воспользуемся тестовой базой данных world, которую можно скачать с официального сайта (//dev.mysql.com/doc/index-other.html) или можете взять из исходников.
В этом уроке мы напишем несколько запросов SQL, в которых решим задачи, озвученные выше. Единственное, вместо товаров у нас будут страны и города, а вместо цен — их население.
Итак, вот эти запросы с комментариями:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
— максимальное кол-во населения в каком-то городе SELECT MAX(Population) FROM City — вся информация о городе с максимальным кол-вом населения SELECT * FROM City WHERE Population = (SELECT MAX(Population) FROM City) — вся информация о городе с минимальным кол-вом населения SELECT * FROM City WHERE Population = (SELECT MIN(Population) FROM City) — 10 городов с наибольшим кол-вом населения SELECT * FROM City ORDER BY Population DESC LIMIT 10 — код Китая SELECT Code FROM Country WHERE Name = ‘China’ — 10 городов Китая с наибольшим кол-вом населения SELECT * FROM City WHERE CountryCode = (SELECT Code FROM Country WHERE Name = ‘China’) ORDER BY Population DESC LIMIT 10 — численность населения всех стран SELECT SUM(Population) FROM Country |
На этом текущий урок завершен. Удачи и до новых встреч!
I have the following table
Table structure:
CREATE TABLE IF NOT EXISTS `people` (
`name` varchar(10) NOT NULL,
`age` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Insert some values:
INSERT INTO `people` (`name`, `age`) VALUES
('bob', 13),
('john', 25),
('steve', 8),
('sue', 13);
Executed Query:
SELECT MAX( `age` ) , `name` FROM `people` WHERE 1
Expected Result:
25, John
Generated Result
25, bob
We can achieve this by using this query
SELECT `age`, `name` FROM `people` ORDER BY age DESC LIMIT 1
Question 1 : What I made mistake here and why this MAX function is not return the relevant row information?
Question 2: Which one is good to use, to increase performance MAX function or ORDER BY clause?
Summary: in this tutorial, you will learn how to use the MySQL MAX() function to get the maximum value in a set of values.
Introduction to MySQL MAX() function
The MySQL MAX() function returns the maximum value in a set of values. The MAX() function comes in handy in many cases such as finding the greatest number, the most expensive product, and the largest payment from customers.
Here is the basic syntax of the MAX() function :
MAX(DISTINCT expression)Code language: SQL (Structured Query Language) (sql)If you add the DISTINCT operator, the MAX() function returns the maximum value of distinct values, which is the same as the maximum value of all values. It means that DISTINCT does not take any effects in the MAX() function.
Notice that DISTINCT has effects in other aggregate functions such as COUNT(), SUM(), and AVG().
MySQL MAX() function examples
We’ll use the payments table in the sample database to demonstration the MAX() function.
A) Using MySQL MAX() function to find the maximum value in a column example
This example uses the MAX() function to return the largest amount of all payments:
SELECT MAX(amount)
FROM payments;Code language: SQL (Structured Query Language) (sql)Try It Out
In this example, the MAX() function checks all values in the amount column of the payments table to find the biggest amount.
B) Using MySQL MAX() function with WHERE clause
This statement uses the MAX() function to find the largest payment in 2004:
SELECT
MAX(amount) largest_payment_2004
FROM
payments
WHERE
YEAR(paymentDate) = 2004;Code language: SQL (Structured Query Language) (sql)In this example:
- First, use a condition in the
WHEREclause to get only payments whose year is 2004. We used theYEAR()function to extract year from the payment date. - Then, use the
MAX()function in theSELECTclause to find the largest amount of payments in 2004.
This picture shows the output:
C) Using MySQL MAX() function in subquery example
To get not only the largest payment’s amount but also other payment’s information such as customer number, check number, and payment date, you use the MAX() function in a subquery as shown in the following query:
SELECT
*
FROM
payments
WHERE
amount = (SELECT
MAX(amount)
FROM
payments);Code language: SQL (Structured Query Language) (sql)Try It Out
How it works.
- The subquery returns the largest amount of all payments.
- The outer query gets the payment whose amount is equal to the largest amount returned from the subquery and also other payment’s information.
Another way to do this without using the MAX() function is to sort the result set in descending order using the ORDER BY clause and get the first row in the result set using the LIMIT clause as follows:
SELECT
*
FROM
payments
ORDER BY amount DESC
LIMIT 1;Code language: SQL (Structured Query Language) (sql)Try It Out
If you don’t have an index on the amount column, the second query with the LIMIT clause is faster because it examines all rows in the payments table, while the first query examines all the rows in the payments table twice, first once in the subquery and another in the outer query.
However, if the amount column is indexed, the first query executes faster.
D) Using MySQL MAX() with GROUP BY clause example
To find the maximum value for every group, you use the MAX function with the GROUP BY clause.
This statement uses the MAX() to get the largest payment of each customer:
SELECT
customerNumber, MAX(amount)
FROM
payments
GROUP BY customerNumber
ORDER BY MAX(amount);Code language: SQL (Structured Query Language) (sql)Try It Out
In this example:
- First, the
GROUP BYclause group payments into groups by customer number. - Second, the
MAX()function returns the largest payment in each group.
E) Using MySQL MAX() with HAVING clause
When you use the MAX() function with the GROUP BY clause, you can find the maximum value for each group.
If you want to filter groups based on a condition, you can use the MAX() function in a HAVING clause.
The following query finds the largest payment of each customer; and based on the returned payments, get only payments whose amounts are greater than 80,000 .
SELECT
customerNumber, MAX(amount)
FROM
payments
GROUP BY customerNumber
HAVING MAX(amount) > 80000
ORDER BY MAX(amount);Code language: SQL (Structured Query Language) (sql)Try It Out
If you want to see the names of customers instead of numbers, you can join the payments table with the customers table:
SELECT
customerName,
MAX(amount)
FROM
payments
INNER JOIN customers USING (customerNumber)
GROUP BY
customerNumber
HAVING
MAX(amount) > 80000
ORDER BY
MAX(amount);Code language: SQL (Structured Query Language) (sql)Here is the output:
In this tutorial, you have learned how to use the MySQL MAX() function to find the maximum value in a set of values.
Was this tutorial helpful?
Let’s say I have the following data in the Customers table: (nothing more)
ID FirstName LastName
-------------------------------
20 John Mackenzie
21 Ted Green
22 Marcy Nate
What sort of SELECT statement can get me the number 22, in the ID column?
I need to do something like this to generate a unique ID. Sure I can let the system do this via auto-increment, but then how would I get the auto generated ID?
I thought of SELECT ID FROM Customers and counting the rows returned but this seems horribly inefficient, and in this case, it will incorrectly return “3”, though I need a unique ID of 23.
Kevin
16.1k8 gold badges56 silver badges74 bronze badges
asked Oct 10, 2009 at 5:14
Robin RodricksRobin Rodricks
110k141 gold badges396 silver badges606 bronze badges
10
You can do
SELECT MAX(ID) FROM Customers;
answered Oct 10, 2009 at 5:15
If you’ve just inserted a record into the Customers table and you need the value of the recently populated ID field, you can use the SCOPE_IDENTITY function. This is only useful when the INSERT has occurred within the same scope as the call to SCOPE_IDENTITY.
INSERT INTO Customers(ID, FirstName, LastName)
Values
(23, 'Bob', 'Smith')
SET @mostRecentId = SCOPE_IDENTITY()
This may or may not be useful for you, but it’s a good technique to be aware of. It will also work with auto-generated columns.
Kevin
16.1k8 gold badges56 silver badges74 bronze badges
answered Oct 10, 2009 at 5:21
David AndresDavid Andres
31.3k7 gold badges45 silver badges36 bronze badges
0
SELECT * FROM Customers ORDER BY ID DESC LIMIT 1
Then get the ID.
McGarnagle
101k31 gold badges226 silver badges259 bronze badges
answered Oct 17, 2012 at 3:37
select max(id) from customers
warren
32.2k21 gold badges85 silver badges122 bronze badges
answered Oct 10, 2009 at 5:15
deostrolldeostroll
11.6k21 gold badges87 silver badges158 bronze badges
To get it at any time, you can do SELECT MAX(Id) FROM Customers .
In the procedure you add it in, however, you can also make use of SCOPE_IDENTITY — to get the id last added by that procedure.
This is safer, because it will guarantee you get your Id–just in case others are being added to the database at the same time.
Pritesh Jain
9,1064 gold badges37 silver badges51 bronze badges
answered Oct 10, 2009 at 5:19
BrisbeBrisbe
1,5782 gold badges20 silver badges42 bronze badges
If you’re talking MS SQL, here’s the most efficient way. This retrieves the current identity seed from a table based on whatever column is the identity.
select IDENT_CURRENT('TableName') as LastIdentity
Using MAX(id) is more generic, but for example I have an table with 400 million rows that takes 2 minutes to get the MAX(id). IDENT_CURRENT is nearly instantaneous…
answered Oct 10, 2009 at 17:34
DamonDamon
6873 silver badges17 bronze badges
3
select max(id) from Customers
warren
32.2k21 gold badges85 silver badges122 bronze badges
answered Oct 10, 2009 at 5:16
monksymonksy
14.1k17 gold badges75 silver badges123 bronze badges
If you are using AUTOINCREMENT, use:
SELECT LAST_INSERT_ID();
Assumming that you are using Mysql:
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
Postgres handles this similarly via the currval(sequence_name) function.
Note that using MAX(ID) is not safe, unless you lock the table, since it’s possible (in a simplified case) to have another insert that occurs before you call MAX(ID) and you lose the id of the first insert. The functions above are session based so if another session inserts you still get the ID that you inserted.
ItsJ0el
551 silver badge5 bronze badges
answered Oct 10, 2009 at 5:33
1
If you’re not using auto-incrementing fields, you can achieve a similar result with something like the following:
insert into Customers (ID, FirstName, LastName)
select max(ID)+1, 'Barack', 'Obama' from Customers;
This will ensure there’s no chance of a race condition which could be caused by someone else inserting into the table between your extraction of the maximum ID and your insertion of the new record.
This is using standard SQL, there are no doubt better ways to achieve it with specific DBMS’ but they’re not necessarily portable (something we take very seriously in our shop).
answered Oct 10, 2009 at 6:53
paxdiablopaxdiablo
847k233 gold badges1568 silver badges1942 bronze badges
You can also use relational algebra. A bit lengthy procedure, but here it is just to understand how MAX() works:
E := πID (Table_Name)
E1 := πID (σID >= ID’ ((ρID’ E) ⋈ E)) – πID (σID < ID’ ((ρID’ E) ⋈ E))
Your answer:
Table_Name ⋈ E1
Basically what you do is subtract set of ordered relation(a,b) in which a<b from A where a, b ∈ A.
For relation algebra symbols see:
Relational algebra
From Wikipedia
answered Jan 24, 2013 at 17:33
ArnabArnab
5551 gold badge7 silver badges12 bronze badges
1
Here’s how I would make the next ID:
INSERT INTO table_name (
ID,
FIRSTNAME,
SURNAME)
VALUES (((
SELECT COALESCE(MAX(B.ID)+1,1) AS NEXTID
FROM table_name B
)), John2, Smith2);
With this you can make sure that even if the table ID is NULL, it will still work perfectly.
answered Jan 12, 2021 at 21:20
Depends on what SQL implementation you are using. Both MySQL and SQLite, for example, have ways to get last insert id. In fact, if you’re using PHP, there’s even a nifty function for exactly that mysql_insert_id().
You should probably look to use this MySQL feature instead of looking at all the rows just to get the biggest insert ID. If your table gets big, that could become very inefficient.
answered Oct 10, 2009 at 6:26
sohumsohum
3,2072 gold badges38 silver badges62 bronze badges
If you want to just select the id use select max(id) from customer.
If you want to select the entire row then use a query like this:
select c1.*
from customer c1, (select max(id) as max_id from customer )c2
where c1.id=c2.max_id
c2 is an alias for the new temporary table which contains max id. Then its cross product is taken with customer table to get the entire row.
Here we are writing a query in the from clause, which can often be quite useful.
answered Jul 7, 2018 at 19:48
To find the next (still not used) auto-increment, I am using this function for somewhat years now.
public function getNewAI($table)
{
$newAI = false;
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if(mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql = "SHOW TABLE STATUS LIKE '".$table."'";
$result = $mysqli->query($sql);
if($result) {
$row = $result->fetch_assoc();
$newAI = $row['Auto_increment'];
}
$mysqli->close();
return $newAI;
}
answered Dec 4, 2019 at 14:55
select * from tablename order by ID DESC
that will give you row with id 22
Ben
51.4k36 gold badges127 silver badges148 bronze badges
answered Aug 23, 2012 at 6:02
1
In PHP:
$sql = mysql_query("select id from customers order by id desc");
$rs = mysql_fetch_array($sql);
if ( !$rs ) { $newid = 1; } else { $newid = $rs[newid]+1; }
thus $newid = 23 if last record in column id was 22.
skuntsel
11.6k11 gold badges44 silver badges67 bronze badges
answered Feb 13, 2015 at 13:27
1
The MySQL MAX() function is an aggregate function that returns the maximum value from an expression.
Typically, the expression would be a range of values returned as separate rows in a column, and you can use this function to find the maximum value from the returned rows. If there are no matching rows, MAX() returns NULL.
For example, you can use this function to find out which city has the largest population out of a list of cities.
Syntax
The syntax of MAX() goes like this:
MAX([DISTINCT] expr) [over_clause]
Where expr is the expression for which you want the maximum value.
The over_clause is an optional clause that works with window functions. Note that the over_clause can only be used if you don’t use the DISTINCT keyword.
The (optional) DISTINCT keyword can be used to eliminate duplicate values.
Basic Example
First, here’s the raw data that we’ll use in this example:
USE world; SELECT Name, Population FROM City WHERE CountryCode = 'THA';
Result:
+-------------------+------------+ | Name | Population | +-------------------+------------+ | Bangkok | 6320174 | | Nonthaburi | 292100 | | Nakhon Ratchasima | 181400 | | Chiang Mai | 171100 | | Udon Thani | 158100 | | Hat Yai | 148632 | | Khon Kaen | 126500 | | Pak Kret | 126055 | | Nakhon Sawan | 123800 | | Ubon Ratchathani | 116300 | | Songkhla | 94900 | | Nakhon Pathom | 94100 | +-------------------+------------+
We can use the MAX() function to find the city with the largest population (i.e. the row with the maximum value in its population column).
USE world; SELECT MAX(Population) AS 'Maximum Value' FROM City WHERE CountryCode = 'THA';
Result:
+---------------+ | Maximum Value | +---------------+ | 6320174 | +---------------+
The GROUP BY Clause
We can use the GROUP BY clause to list out each country, along with the population of that country’s largest city (by population):
USE world; SELECT District, MAX(Population) AS 'Max Value' FROM City WHERE CountryCode = 'AUS' GROUP BY District;
Result:
+-----------------+-----------+ | District | Max Value | +-----------------+-----------+ | New South Wales | 3276207 | | Victoria | 2865329 | | Queensland | 1291117 | | West Australia | 1096829 | | South Australia | 978100 | | Capital Region | 322723 | | Tasmania | 126118 | +-----------------+-----------+
The ORDER BY Clause
We can also use the ORDER BY clause to specify a column with which to order by:
USE world; SELECT District, MAX(Population) AS 'Max Value' FROM City WHERE CountryCode = 'AUS' GROUP BY District ORDER BY `Max Value` ASC;
Result:
+-----------------+-----------+ | District | Max Value | +-----------------+-----------+ | Tasmania | 126118 | | Capital Region | 322723 | | South Australia | 978100 | | West Australia | 1096829 | | Queensland | 1291117 | | Victoria | 2865329 | | New South Wales | 3276207 | +-----------------+-----------+
This orders the results in ascending order, which lists the minimum value first.
Note that, when ordering by a multi-word alias (like `Max Value`), you need to use the backtick character (`) instead of the apostrophe (') to surround the two words.
Find the Maximum Character Length
The MAX() function isn’t limited to just columns with numerical data. You can also combine MAX() with other functions to return maximum values in other areas.
For example, using our sample data, we can find the value with the maximum number of characters in the City column:
SELECT MAX(CHAR_LENGTH(Name)) AS 'Maximum Character Length' FROM city;
Result:
+--------------------------+ | Maximum Character Length | +--------------------------+ | 34 | +--------------------------+
We can also see this by using the following query (which doesn’t involve the MAX() function):
SELECT Name, CHAR_LENGTH(Name) AS 'Character Length' FROM city ORDER BY `Character Length` DESC LIMIT 10;
Result:
+--------------------------------------+------------------+ | Name | Character Length | +--------------------------------------+------------------+ | Luxembourg [Luxemburg/Lëtzebuerg] | 34 | | Castellón de la Plana [Castell | 31 | | San Fernando del Valle de Cata | 30 | | Santo Domingo de los Colorados | 30 | | Thiruvananthapuram (Trivandrum | 30 | | [San Cristóbal de] la Laguna | 29 | | Ingraj Bazar (English Bazar) | 28 | | Soledad de Graciano Sánchez | 28 | | Valle de Chalco Solidaridad | 27 | | Machilipatnam (Masulipatam) | 27 | +--------------------------------------+------------------+
Using an OVER Clause
As mentioned, the syntax allows for an OVER clause to be included in your queries. Basically, the OVER clause allows you to specify how to partition query rows into groups for processing by the window function.
Here’s an example:
SELECT
District,
Name AS City,
Population AS 'City Population',
MAX(Population) OVER(PARTITION BY District) AS 'District Max'
FROM City
WHERE CountryCode = 'AUS'
ORDER BY `District Max` DESC;
Result:
+-----------------+---------------+-----------------+--------------+ | District | City | City Population | District Max | +-----------------+---------------+-----------------+--------------+ | New South Wales | Sydney | 3276207 | 3276207 | | New South Wales | Wollongong | 219761 | 3276207 | | New South Wales | Newcastle | 270324 | 3276207 | | New South Wales | Central Coast | 227657 | 3276207 | | Victoria | Melbourne | 2865329 | 2865329 | | Victoria | Geelong | 125382 | 2865329 | | Queensland | Townsville | 109914 | 1291117 | | Queensland | Brisbane | 1291117 | 1291117 | | Queensland | Cairns | 92273 | 1291117 | | Queensland | Gold Coast | 311932 | 1291117 | | West Australia | Perth | 1096829 | 1096829 | | South Australia | Adelaide | 978100 | 978100 | | Capital Region | Canberra | 322723 | 322723 | | Tasmania | Hobart | 126118 | 126118 | +-----------------+---------------+-----------------+--------------+
This example partitions the rows by District, providing the maximum value for each partition (district). This allows you to see more granular data, such as each city’s population, along with the population of the largest city in the same district.
