Building a join query.

HI,

Need help!..
There are 2 tables, table1 and table2. Table1 has a primary key and it is a foreign key for table2. In table2, for the foreign key,there can be n number of rows or no rows at all.
I need to establish a join between 2 tables based on the key and retrieve the rows irrespective of data present in table2. And if there are
n rows in table2 for the key value, then the maximum value based on a particular column should be retrieved. If there is no row for that key in table2, then a null value should be fetched.

Rows need to be fetched irrespective of data present in table2.[left outer join holds good, but since we need to retrieve the maximum value if there are rows in table2 and null if there are no rows, m stuck.]
Please help.

Did you try GROUP BY and MAX()?

Yes, i tried groupby and max() and it works. But , since i am retrieving 62 columns , i will ve to include include all the 61 columns(except max column) in groupby condition. Is there any other efficient way of implementing the same.

You can do group by in a inline view of second table and left outer join with firsrt table. You can put max on all other columns in second table.

select ...
from table 1
left outer join (select max(), .... from table 2 group by ...) as a
on t1.x=a.x