Finding maxDate for each entity I have a table with (CompanyNumber, File_Date, [various financial results])
I wanted to get only the most recent set of accounts for each company. 

After various googling I eventually got a query that does what I want it to do.

Is this a good way of doing it?  

N.B. the File_Date field is actually a text field, so needs converting to Date before comparisons. Specifically, text --&gt; varchar --&gt; date.

select * from uk_registered_companies.dbo.Filed_Accounts 
JOIN
(
Select companyNumber, max(convert(date, CONVERT(varchar(20),file_date),103)) as MaxDate from uk_registered_companies.dbo.Filed_Accounts
group by CompanyNumber
) mostRecentAccounts
on
Filed_Accounts.CompanyNumber = mostRecentAccounts.CompanyNumber
and
(convert(date, CONVERT(varchar(20),Filed_Accounts.File_Date),103)) = mostRecentAccounts.MaxDate
Quick review, there are a few things I think you could improve, they are mostly a matter of style though.



Casing

Just pick a casing style and stick to it, you are actually using all 3 casing styles kind of randomly. 



Common Table Expression (or, CTE)

In Transact-SQL/ Microsoft SQL Server, there's a syntax sugar type of expression to help reduce sub-queries, the execution plan from the query engine should be the same but they help readability. 

For instance, look at this join clause:


from 
  uk_registered_companies.dbo.Filed_Accounts 
JOIN (
  Select 
      companyNumber, 
      max(convert(date, CONVERT(varchar(20),file_date),103)) as MaxDate 
  from 
      uk_registered_companies.dbo.Filed_Accounts
    group by CompanyNumber
) as mostRecentAccounts



You could do something like this:

--Here is the CTE:
with mostRecentAccounts as (
        select 
            companyNumber, 
            max(convert(date, CONVERT(varchar(20),file_date),103)) as MaxDate 
        from 
            uk_registered_companies.dbo.Filed_Accounts
        group by CompanyNumber
)
--end of CTE
select 
    * 
from 
    uk_registered_companies.dbo.Filed_Accounts
join
    mostRecentAccounts 
  on 
    Filed_Accounts.CompanyNumber = mostRecentAccounts.CompanyNumber
  and
    (convert(date, CONVERT(varchar(20),Filed_Accounts.File_Date),103)) = mostRecentAccounts.MaxDate




Indentation

I did a few changes to your formatting to make it easier to read. For indenting, I usually have the following keywords to the left, and indent everything else accordingly, kind of a crapshoot as there is no official style guide:

with Foo as (
    --...
)
select
  --...
from
  --...
join
  --...
    on --...
where
  --...
group by
  --...
having
 --...
order by
 --...