Hi,
I am having a problem getting my dynamic query to return a specific set of rows. I'm very new to this so excuse me if i've missed something obvious.
Basically, i'm trying to pull a result set out which displays the MAX value of a specific column in the database. The column i'm trying to use the MAX on is called AppID. I have used a spearate dynamic query (shown 1st in this example) to:
I currently have a dynamic query like this.
DynamicQuery queryApplied = qf.Create()
.Select(() => new
{
AppId = ApplicationFields.AppId.ToValue<int>()
})
.Where(PersonFields.PerId == ApplicationFields.PerId)
.From(qf.Application);
var query = qf.Create()
.Select(() => new
{
PerID = PersonFields.PerId.ToValue<string>(),
Forename = PersonFields.PerForeName.ToValue<string>(),
Surname = PersonFields.PerLastName.ToValue<string>(),
DOB = PersonFields.PerDob.ToValue<string>(),
AppID = ApplicationFields.AppId.ToValue<int>()
}).From(qf.Person.LeftJoin(qf.Application)
.On(PersonFields.PerId == ApplicationFields.PerId)
).Where(ApplicationFields.AppId == queryApplied.Max()).GroupBy(PersonFields.PerId, PersonFields.PerForeName, PersonFields.PerLastName, PersonFields.PerDob, ApplicationFields.AppCreatedOn, ApplicationFields.AppId, ApplicationFields.PerId);
executing this results in too many rows being returned, i.e. rows which contain an 'AppID' which is not the MAX 'AppID' for that person.
Basically, i would like the executed SQL to look like this:
SELECT p.PER_ID,p.PER_ForeName, p.PER_LastName, p.PER_DOB, a.APP_CreatedOn, a.APP_ID FROm [PERSON] p
LEFT JOIN [APPLICATION] a
ON
a.PER_ID = p.PER_ID
WHERE APP_ID =
(
SELECT MAX(APP_ID) FROM [APPLICATION]
WHERE PER_ID = p.PER_ID
)
GROUP BY APP_CreatedOn,p.PER_ID, a.Per_ID, p.PER_ForeName, p.PER_LastName, p.PER_DOB, A.APP_CreatedOn, A.APP_ID
I'd really appreciate any help with this one.
Thanks in advance.
Iain