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.
Dear Jose,
The difficulty is that a CustCnt entry can be for a customer, OR CAN BE for a ship-to of a customer. The way I read your post, you want the earliest created CustCnt record for each customer, regardless of ship-to. If thatâs what you want, then the ConNum field will not be unique for your purposes and youâll need to go with something unique like SysRow. Itâs OK if your chosen field is unique in the entire table, as long as itâs unique to a customer which of course is true. Itâs also OK if the chosen field is immaterial to your needs, youâre only using it as a means of strictly-less-than testing on the second copy of your table, and once you find the CustCnt entry for a given customer that has only a null record satisfying the test, then youâve found the record you need.
For more information on the fields of CustCnt and how they work, pull-in the system query zIWCustCnt, and in the Query Builder / Phrase Build tab, click the Erp.CustCnt table, to the right appears field name/type/description which you can right-click copy to Excel.
Also, I realize Iâve given some information on how to solve this issue without really using subqueries. I tried to find a thread on here to reference and donât see one. But Iâll be glad to point you in that direction if youâd like.
HTH
âŚMonty.
Thanks Monty!
With everyones help, i was able to get the one row of the contact per customer. i used subqueries and think i have a handle on it. Will keep practicing but all your help is much appreciated!
Jose