SQL - 如何选择具有最大值的列的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2854257/
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
SQL - How to select a row having a column with max value
提问by Abhishek
date value
18/5/2010, 1 pm 40
18/5/2010, 2 pm 20
18/5/2010, 3 pm 60
18/5/2010, 4 pm 30
18/5/2010, 5 pm 60
18/5/2010, 6 pm 25
i need to query for the row having max(value)(i.e. 60). So, here we get two rows. From that, I need the row with the lowest time stamp for that day(i.e 18/5/2010, 3 pm -> 60)
我需要查询具有最大值（值）（即 60）的行。所以，这里我们得到两行。从那以后，我需要当天时间戳最低的行（即 18/5/2010, 3 pm -> 60）
回答by Sujee
Keywords like TOP, LIMIT, ROWNUM, ...etc are database dependent. Please read this article for more information.
像 TOP、LIMIT、ROWNUM 等关键字依赖于数据库。请阅读这篇文章以获取更多信息。
http://en.wikipedia.org/wiki/Select_(SQL)#Result_limits
http://en.wikipedia.org/wiki/Select_(SQL)#Result_limits
Oracle: ROWNUM could be used.
Oracle：可以使用 ROWNUM。
select * from (select * from table
order by value desc, date_column)
where rownum = 1;
Answering the question more specifically:
更具体地回答这个问题：
select high_val, my_key
from (select high_val, my_key
from mytable
where something = 'avalue'
order by high_val desc)
where rownum <= 1
回答by Jeffrey Kemp
Analytics! This avoids having to access the table twice:
分析！这避免了必须访问表两次：
SELECT DISTINCT
FIRST_VALUE(date_col) OVER (ORDER BY value_col DESC, date_col ASC),
FIRST_VALUE(value_col) OVER (ORDER BY value_col DESC, date_col ASC)
FROM mytable;
回答by TerrorAustralis
Answer is to add a having clause:
答案是添加一个ifying 子句：
SELECT [columns]
FROM table t1
WHERE value= (select max(value) from table)
AND date = (select MIN(date) from table t2 where t1.value = t2.value)
this should work and gets rid of the neccesity of having an extra sub select in the date clause.
这应该可以工作并且摆脱了在日期子句中有一个额外的子选择的必要性。
回答by Rob van Wijk
SQL> create table t (mydate,value)
2 as
3 select to_date('18/5/2010, 1 pm','dd/mm/yyyy, hh am'), 40 from dual union all
4 select to_date('18/5/2010, 2 pm','dd/mm/yyyy, hh am'), 20 from dual union all
5 select to_date('18/5/2010, 3 pm','dd/mm/yyyy, hh am'), 60 from dual union all
6 select to_date('18/5/2010, 4 pm','dd/mm/yyyy, hh am'), 30 from dual union all
7 select to_date('18/5/2010, 5 pm','dd/mm/yyyy, hh am'), 60 from dual union all
8 select to_date('18/5/2010, 6 pm','dd/mm/yyyy, hh am'), 25 from dual
9 /
Table created.
SQL> select min(mydate) keep (dense_rank last order by value) mydate
2 , max(value) value
3 from t
4 /
MYDATE VALUE
------------------- ----------
18-05-2010 15:00:00 60
1 row selected.
Regards, Rob.
问候，罗布。
回答by cartbeforehorse
Technically, this is the same answer as @Sujee. It also depends on your version of Oracle as to whether it works. (I think this syntax was introduced in Oracle 12??)
从技术上讲，这与@Sujee 的答案相同。它还取决于您的 Oracle 版本是否有效。（我认为这种语法是在 Oracle 12 中引入的？？）
SELECT *
FROM table
ORDER BY value DESC, date_column ASC
FETCH first 1 rows only;
As I say, if you look under the bonnet, I think this code is unpacked internally by the Oracle Optimizer to read like @Sujee's. However, I'm a sucker for pretty coding, and nesting selectstatements without a good reason does not qualify as beautiful!! :-P
正如我所说，如果您深入了解引擎盖，我认为此代码是由 Oracle Optimizer 在内部解压缩的，读起来就像 @Sujee 的一样。然而，我对漂亮的编码很着迷，select没有充分理由的嵌套语句不符合美观！！:-P
回答by Eric Leschinski
In Oracle:
在甲骨文中：
This gets the key of the max(high_val) in the table according to the range.
这将根据范围获取表中 max(high_val) 的键。
select high_val, my_key
from (select high_val, my_key
from mytable
where something = 'avalue'
order by high_val desc)
where rownum <= 1
回答by shrinath
In Oracle DB:
在 Oracle 数据库中：
create table temp_test1 (id number, value number, description varchar2(20));
insert into temp_test1 values(1, 22, 'qq');
insert into temp_test1 values(2, 22, 'qq');
insert into temp_test1 values(3, 22, 'qq');
insert into temp_test1 values(4, 23, 'qq1');
insert into temp_test1 values(5, 23, 'qq1');
insert into temp_test1 values(6, 23, 'qq1');
SELECT MAX(id), value, description FROM temp_test1 GROUP BY value, description;
Result:
MAX(ID) VALUE DESCRIPTION
-------------------------
6 23 qq1
3 22 qq
回答by JBrooks
The simplest answer would be
最简单的答案是
--Setup a test table called "t1"
--设置一个名为“t1”的测试表
create table t1
(date datetime,
value int)
-- Load the data. -- Note: date format different than in the question
-- 加载数据。-- 注意：日期格式与问题中不同
insert into t1
Select '5/18/2010 13:00',40
union all
Select '5/18/2010 14:00',20
union all
Select '5/18/2010 15:00',60
union all
Select '5/18/2010 16:00',30
union all
Select '5/18/2010 17:00',60
union all
Select '5/18/2010 18:00',25
-- find the row with the max qty and min date.
-- 找到最大数量和最小日期的行。
select *
from t1
where value =
(select max(value) from t1)
and date =
(select min(date)
from t1
where value = (select max(value) from t1))
I know you can do the "TOP 1" answer, but usually your solution gets just complicated enough that you can't use that for some reason.
我知道你可以做“TOP 1”的答案，但通常你的解决方案会变得足够复杂，以至于由于某种原因你不能使用它。
回答by Nada N. Hantouli
You can use this function, ORACLE DB
你可以使用这个功能，ORACLE DB
public string getMaximumSequenceOfUser(string columnName, string tableName, string username)
{
string result = "";
var query = string.Format("Select MAX ({0})from {1} where CREATED_BY = {2}", columnName, tableName, username.ToLower());
OracleConnection conn = new OracleConnection(_context.Database.Connection.ConnectionString);
OracleCommand cmd = new OracleCommand(query, conn);
try
{
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
result = dr[0].ToString();
dr.Dispose();
}
finally
{
conn.Close();
}
return result;
}
