Group By (generally) or Top(X) what’s the end goal
and an Order By clause
1 Like
with a customer AR invoice, which date is the last date a payment was received. In the past I was able to use CashDtl and instructed the table with Last instead of Each.
You can also use subquery and do min/max calculation in there and a group by, then filter by that in the top query
Hi All, i am trying to accomplish something similar to get some practice but cant seem to get it working on a simple BAQ. I want to show the first record of each customer contact.
I have a BAQ with customer and customer contact. Top level is the Customer and an inner subquery with customer contact. i am able to change the innersubquery to top 1 but the gives me all the customer rows but only contact information on the top row with the rest blank. What piece am i missing?
Thank you for your help!
This post has a good way to do what I call a mini subquery, where you return one item for each row. It works well.
1 Like
The other way to do that, is to have the inner sub run all results, and then have a calculated field that gets a max or min of something, and group by all of the other fields in that query.
Thanks Banderson. i will give this a shot!
I am trying this method,only thing that has me cross is the single column comment. So for my child table, i can only display one column? i need to keep creating the child table with all the different columns i want?
You don’t need to create a new query for each column.
Create a query that returns the top contact for each customer. CustNum and ConNum
Then use the results of that query to join to the contact table to get the rest of the contact record.
edit to use sysrowid
This is was I was trying to say in my message.
select * from Erp.CustCnt inner join
(select CustNum, min(SysRowID) as SysRowID
from Erp.CustCnt
group by custnum) as contactlist on Erp.CustCnt.SysRowID = contactlist.SysRowID
1 Like
Thanks Ken. I been trying at this all day. Idk why this looks so hard in E10 compared to previous versions.
I see the query phrase (thank you for that). i have this query
select
[CustCnt].[Name] as [CustCnt_Name],
[CustCnt].[CustNum] as [CustCnt_CustNum],
[CustCnt].[ConNum] as [CustCnt_ConNum],
[CustCnt].[PhoneNum] as [CustCnt_PhoneNum]
from Erp.CustCnt as CustCnt
showing me all the contacts. Wheni try to use a cal field by creating a min field of the Connum i get Incorrect Syntax.
select
[CustCnt].[Name] as [CustCnt_Name],
[CustCnt].[CustNum] as [CustCnt_CustNum],
[CustCnt].[ConNum] as [CustCnt_ConNum],
[CustCnt].[PhoneNum] as [CustCnt_PhoneNum],
(min( CustCnt.ConNum )) as [Calculated_MIn]
from Erp.CustCnt as CustCnt
group by [CustCnt].[Name],
[CustCnt].[CustNum],
[CustCnt].[ConNum],
[CustCnt].[PhoneNum]
You need to make a inner subquery first.
The min/max/sum you need to do a group by. That is why you are getting the syntax error.
Notice the GroupBy checkbox is checked.
This allows me to do a SUM
Sorry but i am not following you. I created a query with Custcnt with a few columns. I then create a sub inner query with the same table Custcnt. I join the top level query with the inner query.
i then check off the group by connum field in the inner query display columns and create a cal field on the inner query with the min of the connum field?
Here are my screenshots , starting from scratch
with the phrase
select
[CustCnt].[Company] as [CustCnt_Company],
[CustCnt].[CustNum] as [CustCnt_CustNum],
[CustCnt].[ConNum] as [CustCnt_ConNum],
[CustCnt].[Name] as [CustCnt_Name],
[CustCnt].[PhoneNum] as [CustCnt_PhoneNum]
from Erp.CustCnt as CustCnt
Now i am going to create subquery of the same table CustCnt
Now i am going to join the queries
am i on the right track?
Thank you
I usually create the subquery first. That way I can tell if I am going to be getting the correct data.
Check out this BAQ.
TestCustCnt.baq (21.0 KB)
Thank you Ken! Reviewing it. Thanks for sending it and taking the time to show me!
ken is it fair to say that whenever i want to use the First statement in a BAQ i will need to use the SysRowID field as my “filter”. I tried using the ConNum/CustNum and some other fields and never got a single row per customer. Thanks for all your help!
Maybe is because i am getting use to the new feature of 10 but seems like getting rid of the each, first, last statement made things more complicated than easier to do!
jose
It’s just what you are used to. If I had to make a query in E-9 I would be totally confused, but that’s because I started with E-10. You’ll get the hang of it! We’ll help.
2 Likes
I would not think that you would use the sysrowid each time. I was just using that field for this example.
There could be other fields that you might want to use. You have to make that decision by looking at the table first.
I think that was because you were using the subquery top which only returns 1 row (or whatever you have set) for for whole subquery. Not because of the field you were using. Using the group by will act like what you describe used to be first each last in E-9.
1 Like