SQL: How to Return One DB Row from Two That Have The Same Values In Opposite Columns Using the MAX Function?

Question

I have three columns in a table - ID, Column1, Column2 - with this example data:

ID  Column1 Column2
----------------------
1   1       2  
2   2       1  
3   4       3  
4   3       4

Since, in the first two rows, Column1 and Column2 have the same values (but in different columns), I want my MAX query to return an ID of 2. Same thing with rows 3 and 4 .... since Columns 1 and 2 have the same values (but in different columns), I want MAX(ID) to return 4. Of course, with MAX, you use Group By, but that will not work in my case.

In effect, I need a Group By to work across two columns. Is this possible? If not, what's the best way to accomplish getting the IDs of 2 and 4 given the matching values that are in different columns?

Answer 1

You can use a "CASE"

select id, case when column1 > column2 then column1 else column2 end
  from your_table
 where whatever

Your question is not very clear, so this answer may not be appropriate. This would give you a table of "id" values along with the greater of "column1" and "column2" for that "id".

Answer 2

An alternative to use CASE would be to UNION the results of the column(s), and perform a MAX on them:

  SELECT z.id,
         MAX(z.col)
    FROM (SELECT x.id,
                 x.column1 AS col
            FROM TABLE x
          UNION 
          SELECT y.id,
                 y.column2
            FROM TABLE y) z
GROUP BY z.id

Answer 3

Not exactly sure what you need but some thing on the lines of see below should work (You group by greater of col1 and col2 followed by lesser of col1 and col2

select max(id) from (
select 1 as id,  1  as col1,     2  as col2 union all
select 2 ,  2  ,     1  union all
select 3  , 4    ,   3  union all
select 4 ,  3   ,    4) as x
group by
case when col1>=col2 then col1 else col2 end,
case when col1<col2 then col1 else col2 end