Sub-query alias

Mar 4 at 10:48 AM
Hi, can anyone please assist me in coding this simple query (below). Regardless of the combinations I try I can't alias the Field3 subquery without causing exceptions.

SELECT TOP 3
Field1, Field2,
(
  SELECT TOP 1 OtherField FROM Table2 ORDER BY OtherField DESC
) as Field3,
Field4
From Table1;

The C# i'm using for this example follows:

DBQuery querySub = DBQuery.Select()
.TopN(1).Fields("OtherField")
.From("Table2")
.OrderBy("OtherField", ORM.Data.Order.Descending);

DBQuery queryMain = DBQuery.Select()
.TopN(3)
.Field("Field1")
.Field("Field2")
.Select(querySub).As("Field3")
.Field("Field4")
.From("Table1");

Any help with this is greatly appreciated.
Mar 4 at 3:30 PM
I managed to get this working by re-structuring the SQL and subsequent C# with a left join.