Sub query size restrictions

[WildlyHarry]

I have a large table that contains account data and the dates each account
has hit a violation report. Each record of my table has details about the
account and its violation. It is possible for an account to hit the
violation report multiple (1000's) times. I am trying to write a query that
will return the most recent violation report id for each account in the
table. The issue I am having is that the query fails when I run it against
the full table. It seems to run fine up to 10,000 records. Anything after
that and it tells me that the query can only return one record. My table is
over 300,000 records and will continue to grow. Below is my query

SELECT DISTINCT test.[Account Number], (select top 1 t.[id] from [test]
AS t where t.[Account Number] = [test].[Account Number] and t.Created >
[test].Created ORDER BY t.Created DESC, t.[account number]) AS ID
FROM test;

Any thoughts on how I could solve my issue?

 

[KARL DEWEY]

Try this --
SELECT test.[Account Number], Max(test.Created) AS Latest
FROM test
GROUP BY test.[Account Number];

 

[KARL DEWEY]

Try this --
SELECT test.[Account Number], Max(test.Created) AS Latest
FROM test
GROUP BY test.[Account Number];