10 Replies Latest reply on Jun 30, 2009 9:32 PM by Rob van Wijk

# SQL Max of 2 columns

Hi All,

I need some help in getting max of two columns, in the sample datasets below I wanted to get the max of col3, if there are multiple max values on col3 then have to get the max of col4.

Sample Dataset 1
col1     col2     col3     col4
A     1     6     1
B     1     3     1
C     1     1     1
D     1     1     2
E     1     1     3
F     1     1     4
G     1     1     5

In the first case the expected output is -- since col3 has 6 as the maximum value.
col1     col2     col3     col4
A     1     6     1

Sample Dataset 2
col1     col2     col3     col4
C     1     1     1
D     1     1     2
E     1     1     3
F     1     1     4
G     1     1     5

In the second case the expected output is -- since col3 has same value for all and col4 has 5 as the max value.
col1     col2     col3     col4

Hope I am explaining it better way, Please let me know if anyone can help me.

Thanks,
G     1     1     5
• ###### 1. Re: SQL Max of 2 columns
Hi,

You can use greatest .
http://www.techonthenet.com/oracle/functions/greatest.php

Regards salim
• ###### 2. Re: SQL Max of 2 columns
``````SQL> create table mytable (col1,col2,col3,col4)
2  as
3  select 'A', 1, 6, 1 from dual union all
4  select 'B', 1, 3, 1 from dual union all
5  select 'C', 1, 1, 1 from dual union all
6  select 'D', 1, 1, 2 from dual union all
7  select 'E', 1, 1, 3 from dual union all
8  select 'F', 1, 1, 4 from dual union all
9  select 'G', 1, 1, 5 from dual
10  /

Tabel is aangemaakt.

SQL> select max(col1) keep (dense_rank last order by col3,col4) col1
2       , max(col2) keep (dense_rank last order by col3,col4) col2
3       , max(col3) col3
4       , max(col4) keep (dense_rank last order by col3,col4) col4
5    from mytable
6  /

C       COL2       COL3       COL4
- ---------- ---------- ----------
A          1          6          1

1 rij is geselecteerd.

SQL> delete mytable where col1 in ('A','B')
2  /

2 rijen zijn verwijderd.

SQL> select max(col1) keep (dense_rank last order by col3,col4) col1
2       , max(col2) keep (dense_rank last order by col3,col4) col2
3       , max(col3) col3
4       , max(col4) keep (dense_rank last order by col3,col4) col4
5    from mytable
6  /

C       COL2       COL3       COL4
- ---------- ---------- ----------
G          1          1          5

1 rij is geselecteerd.``````
Regards,
Rob.
• ###### 3. Re: SQL Max of 2 columns
First Result
``````SQL> with dat as (
2   select 'A' col1,1 col2,6 col3,1 col4 from dual union all
3   select 'B',1,3,1 from dual union all
4   select 'C',1,1,1 from dual union all
5   select 'D',1,1,2 from dual union all
6   select 'E',1,1,3 from dual union all
7   select 'F',1,1,4 from dual union all
8   select 'G',1,1,5 from dual
9   )
10   select dat.* from dat ,
11   (select greatest(max(col2) , max(col3), max(col4)) maxx
12   from dat )
13*  where col2=maxx or col3=maxx or col4=maxx

C       COL2       COL3       COL4
- ---------- ---------- ----------
A          1          6          1``````
Ss
• ###### 4. Re: SQL Max of 2 columns
Second Test for sample 2
``````SQL> with dat as (
2   select 'C' col1,1 col2,1 col3,1 col4 from dual union all
3   select 'D',1,1,2 from dual union all
4   select 'E',1,1,3 from dual union all
5   select 'F',1,1,4 from dual union all
6   select 'G',1,1,5 from dual
7   )
8   select dat.* from dat ,
9   (select greatest(max(col2) , max(col3), max(col4)) maxx
10   from dat )
11*  where col2=maxx or col3=maxx or col4=maxx

C       COL2       COL3       COL4
- ---------- ---------- ----------
G          1          1          5``````
SS
• ###### 5. Re: SQL Max of 2 columns
Wrong post

Edited by: Salim Chelabi on 2009-06-30 08:54
• ###### 6. Re: SQL Max of 2 columns
Hi,

more correct than my last post.

Regards salim.
``````SELECT col1, col2, col3, col4
FROM (SELECT t.*,
ROW_NUMBER () OVER (PARTITION BY NULL ORDER BY greatest(col2,col3,col4) desc
) rn
FROM t)
WHERE rn = 1``````
``````WITH t AS
(SELECT 'A' col1, 1 col2, 6 col3, 1 col4
FROM DUAL
UNION ALL
SELECT 'B', 1, 3, 1
FROM DUAL
UNION ALL
SELECT 'C', 2, 1, 1
FROM DUAL
UNION ALL
SELECT 'D', 1, 1, 2
FROM DUAL
UNION ALL
SELECT 'E', 1, 1, 3
FROM DUAL
UNION ALL
SELECT 'F', 1, 1, 4
FROM DUAL
UNION ALL
SELECT 'G', 1, 1, 5
FROM DUAL)
SELECT col1, col2, col3, col4
FROM (SELECT t.*,
ROW_NUMBER () OVER (PARTITION BY NULL ORDER BY greatest(col2,col3,col4) desc
) rn
FROM t)
WHERE rn = 1

C       COL2       COL3       COL4
- ---------- ---------- ----------
A          1          6          1

1 row selected.``````
``````WITH t AS
(      SELECT 'C' col1, 1 col2, 1 col3, 1 col4
FROM DUAL
UNION ALL
SELECT 'D', 1, 1, 2
FROM DUAL
UNION ALL
SELECT 'E', 1, 1, 3
FROM DUAL
UNION ALL
SELECT 'F', 1, 1, 4
FROM DUAL
UNION ALL
SELECT 'G', 1, 1, 5
FROM DUAL)
SELECT col1, col2, col3, col4
FROM (SELECT t.*,
ROW_NUMBER () OVER (PARTITION BY NULL ORDER BY greatest(col2,col3,col4) desc
) rn
FROM t)
WHERE rn = 1

C       COL2       COL3       COL4
- ---------- ---------- ----------
G          1          1          5

1 row selected.``````
Edited by: Salim Chelabi on 2009-06-30 08:59

Edited by: Salim Chelabi on 2009-06-30 08:59
• ###### 7. Re: SQL Max of 2 columns
Hi Sanjay,

But you scan two times dat table.

Regards Salim.
• ###### 8. Re: SQL Max of 2 columns
Thanks Everyone!!

I missed to update one more condition...

In case the we have a data as below

C COL2 COL3 COL4
- ---------- ---------- ----------
A 1 1 15
B 1 6 1

I would like to have the output as, since we have a higher value in the col3. Only if we have duplicates in col3 the col4 needs to be validated.

B 1 6 1

• ###### 9. Re: SQL Max of 2 columns
Try this query.

``````SELECT col1, col2, col3, col4
FROM (SELECT t.*,
ROW_NUMBER () OVER (PARTITION BY NULL ORDER BY GREATEST
(col2,
col3
) DESC,
col4 DESC) rn
FROM t)
WHERE rn = 1``````
``````WITH t AS
(SELECT 'A' col1, 1 col2, 1 col3, 15 col4
FROM DUAL
UNION ALL
SELECT 'B', 1, 6, 1
FROM DUAL)
SELECT col1, col2, col3, col4
FROM (SELECT t.*,
ROW_NUMBER () OVER (PARTITION BY NULL ORDER BY GREATEST
(col2,
col3
) DESC,
col4 DESC) rn
FROM t)
WHERE rn = 1

C       COL2       COL3       COL4
- ---------- ---------- ----------
B          1          6          1

1 row selected.

WITH t AS
(SELECT 'A' col1, 1 col2, 1 col3, 15 col4
FROM DUAL
UNION ALL
SELECT 'B', 1, 1, 1
FROM DUAL)
SELECT col1, col2, col3, col4
FROM (SELECT t.*,
ROW_NUMBER () OVER (PARTITION BY NULL ORDER BY GREATEST
(col2,
col3
) DESC,
col4 DESC) rn
FROM t)
WHERE rn = 1

C       COL2       COL3       COL4
- ---------- ---------- ----------
A          1          1         15

1 row selected.``````
• ###### 10. Re: SQL Max of 2 columns
Prasath wrote:
I missed to update one more condition...
This extra condition doesn't change the solution. Please try and validate.

Regards,
Rob.