Using the max function in a dynamic query

Posts   
 
    
TopDog74
User
Posts: 40
Joined: 27-Apr-2012
# Posted on: 30-Apr-2012 15:40:55   

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

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 30-Apr-2012 17:56:22   

Could you post the query that's being executed by the queryspec query you posted? (please enable DQE tracing (see troubleshooting and debugging) to get this query)

Frans Bouma | Lead developer LLBLGen Pro
TopDog74
User
Posts: 40
Joined: 27-Apr-2012
# Posted on: 01-May-2012 12:32:30   

I seem to be unable to get my query to execute now. Given i have two entities Person, and Application, can you please suggest how i would write my dynamic query to execute the T-SQL shown in my post.

Thanks

Iain

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 01-May-2012 19:34:34   

I seem to be unable to get my query to execute now.

What do you mean by it's not working? Is there some exception thrown, or it doesn't compile. Otherwise there should be a query generated, even if it's not returning what you are expecting.