How to select multiple column with one column being max() from multiple tables

Question

select max(some_column1), some_column2 from(
    select distinct some_column1, some_column2 from table1 where some_column1 = (select max(some_column1) from table1)
    union all
    select distinct some_column1, some_column2 from table2 where some_column1 = (select max(some_column1) from table2) 
    union all
    select distinct some_column1, some_column2 from table3 where some_column1 = (select max(some_column1) from table3)
) as max_result

This query does exactly what I want. The only problem is when I get the max result from some_column1 I also want to get some_column2 that is corresponds to max(some_column1). Instead I get 3 results. I need to limit it to one result. some_column2 has to be from the same table as max(some_column1). Is there anyway to do this besides storing the result into a temp table? All help is appreciated. Thanks in advance!

Answer 1

I think the simplest way is to sort by some_column1 and take the first row. This will get the row where some_column1 has the bigger value and still has access to some_column2.

Try:

select top 1 some_column1, some_column2 from(
    select distinct some_column1, some_column2 from @table1 where some_column1 = (select max(some_column1) from @table1)
    union all
    select distinct some_column1, some_column2 from @table2 where some_column1 = (select max(some_column1) from @table2) 
    union all
    select distinct some_column1, some_column2 from @table3 where some_column1 = (select max(some_column1) from @table3)
) as max_result
order by some_column1 desc