SQL 如何在不使用 MAX 函数的情况下找到列中的最大值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5373885/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I find maximum in a column without using MAX function?
提问by Jamal
I was asked this question by a friend. You are given a table with just one field which is an integer. Can you the highest value in the field without using the MAX function ?
一个朋友问我这个问题。您将获得一张只有一个字段的表，该字段是一个整数。你能在不使用 MAX 函数的情况下获得该领域的最高值吗？
I think we can change the sign of each column and find the minimum using MIN function. Is that correct?
我认为我们可以更改每列的符号并使用 MIN 函数找到最小值。那是对的吗？
回答by codaddict
Yes. You can do that as:
是的。你可以这样做：
select MIN(-1 * col)*-1 as col from tableName;
Alternatively you can use the LIMITclause if your database supports it.
或者，LIMIT如果您的数据库支持，您可以使用该子句。
One more alternative is to use a self-join of the table. Consider the query:
另一种选择是使用表的自联接。考虑查询：
select A.col, B.col
from tableName as A, tableName as B
where A.col < B.col
It find all possible pairs and retains only those pairs where first field is less than second field. So your max value will not appear in the first field as it is not less that any other value.
它找到所有可能的对，并只保留第一个字段小于第二个字段的那些对。因此，您的最大值不会出现在第一个字段中，因为它不小于任何其他值。
You can make use of the above query as a subquery to select only the max value as:
您可以使用上述查询作为子查询来仅选择最大值为：
select col from tableName where col not in
( select A.col from tableNAme as A, tableName as B
where A.col < B.col)
If the table has multiple max values, the above query will return them all. To fix this you can use distinctin the outer select.
如果表有多个最大值，则上述查询将全部返回。要解决此问题，您可以distinct在外部选择中使用。
回答by btilly
Let's pile on with more ways to do it!
让我们用更多的方法来做到这一点！
SELECT DISTINCT t1.col
FROM tableName t1
LEFT JOIN tableName t2
ON t2.col > t1.col AND t2.col IS NOT NULL
WHERE t2.col IS NULL
回答by OMG Ponies
Why you would ignore using the function supported on any database is anyone's guess, especially if you'd use the MIN function, but...
为什么你会忽略使用任何数据库支持的函数是任何人的猜测，特别是如果你使用 MIN 函数，但是......
GREATEST
最伟大的
...some database vendors support the GREATEST function:
...一些数据库供应商支持 GREATEST 函数：
SELECT GREATEST(column)
FROM ...
GREATESTreturns the highest value, of all the columns specified. Those that support GREATESTinclude:
GREATEST返回指定的所有列中的最大值。支持的GREATEST包括：
TOP/LIMIT
上限/下限
TOPis SQL Server (2000+) only:
TOP仅限 SQL Server (2000+)：
SELECT TOP 1 column
FROM YOUR_TABLE
ORDER BY column DESC
LIMITis only supported by MySQL, PostgreSQL and SQLite
LIMIT仅 MySQL、PostgreSQL 和 SQLite 支持
SELECT column
FROM YOUR_TABLE
ORDER BY column DESC
LIMIT 1
ROW_NUMBER
ROW_NUMBER
ROW_NUMBER is supported by PostgreSQL 8.4+, Oracle 9i+, SQL Server 2005+:
PostgreSQL 8.4+、Oracle 9i+、SQL Server 2005+ 支持 ROW_NUMBER：
SELECT x.col
FROM (SELECT column AS col,
ROW_NUMBER() OVER (ORDER BY column DESC) AS rank
FROM YOUR_TABLE) x
WHERE x.rank = 1
回答by paxdiablo
Yes, you could negate everything and then use min.
是的，您可以否定所有内容，然后使用min.
That would, of course, end up giving you the negation of the value you want, which you would then have to negate again to get the actual value.
当然，这最终会否定您想要的值，然后您必须再次否定它才能获得实际值。
Of course, other than academic interest, I'd have a hard time believing you'd find a DBMS with a minfunction and no max.
当然，除了学术兴趣之外，我很难相信你会找到一个带有min函数而没有max.
You could also order by that column descending and then just extract the first row, something like:
您也可以按该列降序排序，然后只提取第一行，例如：
select my_column from my_table
order by my_column desc
fetch first row only;
回答by DhruvPathak
use order by number desc limit 1
使用 order by number desc limit 1
回答by Shamail
This query below could also get you the maximum for a specific column without using the MAX function.
下面的这个查询还可以让您在不使用 MAX 函数的情况下获得特定列的最大值。
select top 1 (col) from tablename order by col desc
按 col desc 从 tablename order 中选择 top 1 (col)
