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 --> varchar --> 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_Accountsgroup by CompanyNumber) mostRecentAccountsonFiled_Accounts.CompanyNumber = mostRecentAccounts.CompanyNumberand(convert(date, CONVERT(varchar(20),Filed_Accounts.File_Date),103)) = mostRecentAccounts.MaxDate
Finding maxDate for each entity
sql;datetime;sql server
Quick review, there are a few things I think you could improve, they are mostly a matter of style though.CasingJust 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 mostRecentAccountsYou 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 CTEselect     * from     uk_registered_companies.dbo.Filed_Accountsjoin    mostRecentAccounts   on     Filed_Accounts.CompanyNumber = mostRecentAccounts.CompanyNumber  and    (convert(date, CONVERT(varchar(20),Filed_Accounts.File_Date),103)) = mostRecentAccounts.MaxDateIndentationI 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 --...