hi again,

this achieves it:

sql Syntax (Toggle Plain Text)

with temp_table ( name, in_date, out_date )
  as ( SELECT name, in_date, coalesce(out_date, '9090-09-09') 
     as out_date FROM date_table)
        SELECT * FROM temp_table x
           WHERE out_date = (SELECT max(out_date) FROM temp_table y 
              WHERE x.name = y.name)

with temp_table ( name, in_date, out_date ) as ( select name, in_date, coalesce(out_date, '9090-09-09') as out_date from date_table) select * from temp_table x where out_date = (select max(out_date) from temp_table y where x.name = y.name)

With WITH clause table date_table will be duplicated and all null values of out_date replaced by funny date 9th September, 9090. Replacement is necessary because max() ignores NULL values. I didn't test it with MS SQL Server, but I know SQL Server 2005 also features WITH clause and coalesce function. Possibly date constant must be rewritten, or replace it by getdate().

You can give me a reply, if this also works on MS SQL Server 2005 properly.

krs,
tesu