Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Unable to fetch data f...
Microsoft Dynamics AX (Archived)

Unable to fetch data for other columns when using group by clause

(0) ShareShare
ReportReport
Posted on by manasa133 110

Hi All,

I have a scenario where in i need to get the distinct record from a table. If i use 'group by' clause, then i need to select the columns which i'm using in the group by clause. 

Ex--

Table Table1;

while select Voucher, RecId from Table1 group by Table1 .Voucher
{

}

I need to group by only on Vouchers and not with RecId. But if i dont pass RecId to group by clause, the query doesn't gives the expected results. My requirement is to get the RecId and Voucher from the query on grouping by vouchers only. How do i achieve this?

*This post is locked for comments

  • Keshav Kumar Profile Picture
    Keshav Kumar 220 on at
    RE: Unable to fetch data for other columns when using group by clause

    Hi Manasa

    Whenever you are using group by ,use aggregate functions such as max of, min of, sum etc. in your code.Use below code to get your results.

    Table table1

    while select max of(RecId),voucher from Table1 group by Table1 .Voucher

    {

    }

  • Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: Unable to fetch data for other columns when using group by clause

    And ievgen suggestion also works

    Table1 t1;
    
while select maxOf(recid) from t1
   group by Voucher
{
   info(strFmt("%1 - %2", t1.Voucher, t1.RecId));
}

    And as ievgen mentioned, this approach is better than 2 selects because you doing only one, that is obviously faster

  • Suggested answer
    manasa133 Profile Picture
    manasa133 110 on at
    RE: Unable to fetch data for other columns when using group by clause

    Table1 t1, t2;

    while select t1

       group by Voucher

    {

       select firstOnly Voucher, RecId from t2

           where t2.Voucher == t1.Voucher;

       info(strFmt("%1 - %2", t2.Voucher, t2.RecId));

    }

    This works! Thanks :)

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Unable to fetch data for other columns when using group by clause

    You cannot group by 1 field and select 2, it's not an AX issue, it just does not make sense in general. However, if you have 2 records with same voucher and you want to get 1 recid and don't really care first or second you can do something like:

    while select Voucher, MAXOF(RecId) from Table1

    group by Table1 .Voucher

    {

    }

    This approach is better than 2 selects because you doing only one, that is obviously faster.

  • Verified answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: Unable to fetch data for other columns when using group by clause

    Try this:

    Table1 t1, t2;
    
while select t1
    group by Voucher
{
    select firstOnly RecId from t2
        where t2.Voucher == t1.Voucher;
        
    info(strFmt("%1 - %2", t2.Voucher, t2.RecId));
}


  • manasa133 Profile Picture
    manasa133 110 on at
    RE: Unable to fetch the data for all the columns when using group by clause

    I expect one record with one voucher number, along with the RecId of that record

  • Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: Unable to fetch the data for all the columns when using group by clause

    Hi manasa133!

    If there are several rows with one voucher num and different recId? In this case what you expect?

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