10 Replies Latest reply: Jun 30, 2009 4:32 PM by Rob van Wijk RSS

    SQL Max of 2 columns

    674955
      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
          666352
          Hi,

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

          Regards salim
          • 2. Re: SQL Max of 2 columns
            Rob van Wijk
            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
              SanjayRs
              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
                SanjayRs
                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
                  666352
                  Wrong post

                  Edited by: Salim Chelabi on 2009-06-30 08:54
                  • 6. Re: SQL Max of 2 columns
                    666352
                    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
                      666352
                      Hi Sanjay,

                      But you scan two times dat table.

                      Regards Salim.
                      • 8. Re: SQL Max of 2 columns
                        674955
                        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

                        Thanks in advance.
                        • 9. Re: SQL Max of 2 columns
                          666352
                          Try this query.

                          Regrads salim.
                          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
                            Rob van Wijk
                            Prasath wrote:
                            I missed to update one more condition...
                            This extra condition doesn't change the solution. Please try and validate.

                            Regards,
                            Rob.