Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Using group by and ord...
Microsoft Dynamics AX (Archived)

Using group by and order by at the same time in In memory table

(0) ShareShare
ReportReport
Posted on by Hossein.K 6,642

Hi guys,

How to use group by and order by at the same time in in memory table?

           while
           select sum(RemainAmount),sum(InterestAmount),DueDate
              from     tmpInterestNote
              order by tmpInterestNote.DueDate asc
              group by tmpInterestNote.DueDate
              
              {
                  custInterestInfoLine = new CustInterestInfoLine();
                  custInterestInfoLine.parmDueDate(tmpInterestNote.DueDate);
                  custInterestInfoLine.parmInterestAmount(tmpInterestNote.InterestAmount);
                  custInterestInfoLine.parmRemainAmount(tmpInterestNote.RemainAmount);
                  custInterestInfoLineList.addStart(custInterestInfoLine);
              }


Error:Temporary tables can have either Group By or Order By fields, but not both.

*This post is locked for comments

  • Hossein.K Profile Picture
    Hossein.K 6,642 on at
    RE: Using group by and order by at the same time in In memory table

    Hi Rustem Galiamov

    thanks for reply

  • Verified answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: Using group by and order by at the same time in In memory table

    Hi Hossein.Karimi!

    You can use asc or desc ordering with group by. Try this:

    while select sum(RemainAmount), sum(InterestAmount) from tmpInterestNote
    group by DueDate asc (or desc)
{ 
    custInterestInfoLine = new CustInterestInfoLine();
    custInterestInfoLine.parmDueDate(tmpInterestNote.DueDate);
    custInterestInfoLine.parmInterestAmount(tmpInterestNote.InterestAmount);
    custInterestInfoLine.parmRemainAmount(tmpInterestNote.RemainAmount);
    custInterestInfoLineList.addStart(custInterestInfoLine);
}
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,269 Super User 2024 Season 2 on at
    RE: Using group by and order by at the same time in In memory table

    Hi Hossein,

    I don't know how this temporary table is filled and for what reason. Possibly a query on the original tables would be an option, but then you have to tell how this temporary table is filled.

  • Hossein.K Profile Picture
    Hossein.K 6,642 on at
    RE: Using group by and order by at the same time in In memory table

    Hi André,

    thanks for your reply. your solution is work around and perfect!

    There is no direct way?

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,269 Super User 2024 Season 2 on at
    RE: Using group by and order by at the same time in In memory table

    Hi Hossein,

    You can ensure that the temporary table is filled already with cumulated amounts per due date. Eventually insert the result of a group by in a second temporary table. Then you can use the sorting in two steps.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans