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