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

(0) Share

Report

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

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Rustem Galiamov 8,072 on at

Like (0)

Report
Copy link

Link copied!

Hi manasa133!

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

Was this reply helpful? Yes No
manasa133 110 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

Rustem Galiamov 8,072 on at

Like (1)

Report
Copy link

Link copied!
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)); }

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,286 on at

Like (3)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Suggested answer

manasa133 110 on at

Like (0)

Report
Copy link

Link copied!

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 :)

Was this reply helpful? Yes No
Rustem Galiamov 8,072 on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Keshav Kumar 220 on at

Like (0)

Report
Copy link

Link copied!

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

{

}

Was this reply helpful? Yes No