英文:
Select max value row(s) based on 2 columns
问题
在第一种情况下，最大值30在第3行，因此返回第3行：
C, 30, 12
在第二种情况下，最大值在第2行，因此返回第2行：
B, 2, 31
在最后一种情况下，最大值分别在第2行和第3行，因此返回两者：
B, 2, 31
C, 31, 12
英文:
How to select row(s) with the max value in either c2nd or 3rd column?
In first case, the max value 30 is in row3, hence returning row3
A, 1, 10
B, 2, 11
C, 30, 12
=> C, 30, 12
In second case, max value is on row2, hence returning row2
A, 1, 10
B, 2, 31
C, 30, 12
=> B, 2, 31
In last case, max value is on both row2 and row3, hence returning both
A, 1, 10
B, 2, 31
C, 31, 12
=> B, 2, 31
=> C, 31, 12
答案1
得分: 2
从 Oracle 12 版本开始，您可以在 ORDER BY 子句中使用 GREATEST，然后使用 FETCH FIRST ROW WITH TIES 来仅查询一次表格：
SELECT *
FROM table_name
ORDER BY GREATEST(column2, column3) DESC
FETCH FIRST ROW WITH TIES
在 Oracle 12 之前，您可以使用 RANK 分析函数（同样只查询一次表格）：
SELECT *
FROM (
SELECT t.*,
RANK() OVER (ORDER BY GREATEST(column2, column3) DESC) AS rnk
FROM table_name t
)
WHERE rnk = 1;
对于示例数据：
CREATE TABLE test (c1, c2, c3) AS
SELECT 'a', 1, 1 FROM DUAL UNION ALL
SELECT 'b', 2, 3 FROM DUAL UNION ALL
SELECT 'c', 3, 2 FROM DUAL;
两者都会输出：
| C1 | C2 | C3 |
|---|---|---|
| b | 2 | 3 |
| c | 3 | 2 |
英文:
From Oracle 12, you can use GREATEST in the ORDER BY clause and then FETCH FIRST ROW WITH TIES to only query the table once:
SELECT *
FROM table_name
ORDER BY GREATEST(column2, column3) DESC
FETCH FIRST ROW WITH TIES
Before Oracle 12, you can use the RANK analytic function (again, only querying the table once):
SELECT *
FROM (
SELECT t.*,
RANK() OVER (ORDER BY GREATEST(column2, column3) DESC) AS rnk
FROM table_name t
)
WHERE rnk = 1;
Which, for the sample data:
CREATE TABLE test (c1, c2, c3) AS
SELECT 'a', 1, 1 FROM DUAL UNION ALL
SELECT 'b', 2, 3 FROM DUAL UNION ALL
SELECT 'c', 3, 2 FROM DUAL;
Both output:
| C1 | C2 | C3 |
|---|---|---|
| b | 2 | 3 |
| c | 3 | 2 |
答案2
得分: 2
你可以执行：
使用 m 作为 (选择最大(greatest(col2, col3))作为 g from t)
从 t 中选择 * 跨连接 m
其中 t.col2 = m.g 或 t.col3 = m.g
英文:
You can do:
with m as (select max(greatest(col2, col3)) as g from t)
select * from t cross join m
where t.col2 = m.g or t.col3 = m.g
答案3
得分: 2
只需使用where子句，使用一个不相关的子查询来检查符合条件的行：
select *
from t
where (select max(greatest(c2,c3)) from t) in (c2, c3)
英文:
Or just use a where clause to check for qualifying rows using an uncorrelated subquery
select *
from t
where (select max(greatest(c2,c3)) from t) in (c2, c3)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论