Nested Select in SQL Server

Hope this works as expected as i wanted an alternative to creating temp table to get the distinct values.


select top 100 * from (
select distinct a.account_id, 
                                     a.account_type as newapp_at, 
                                     c.account_type as myapp_at,    
                                     c.acct_modified_date 
from dbo.account a (nolock), dbo.account_xref b (nolock), 
                        dbo.account_stage_arch c (nolock)
where a.account_id = b.account_id
    and b.source_key = c.source_key
    and isnull (a.account_type, '') = ''
    and isnull (c.account_type, '') = ''
    and isnull (b.end_date, '') = ''
    and b.source_system = 'MYAPP') x
order by acct_modified_date desc;

Well i can also use the RANK function to get it done.

Hope this helps....