How to select the first record based on primary key

Epicor ERP 10
1

Hi Everyone,

I’m trying to create a BAQ to show all vendor list along with their contacts.
My current scenario is as follow:

Table = ERP.Vendor linked to ERP.VendorCnt

each vendor have multiple VendorCnt records, how do I display only the first vendorCnt records for every Vendor records? I’ve tried something like distinctTop but it doesn’t seem to work.

image

image

Example of current result:
Vendor = ABC
Contact = Lion, Tiger, Apple

Expected result:
Vendor = ABC
Contact = Lion

Thanks in advance!

2 
select 
	[Vendor].[VendorNum] as [Vendor_VendorNum],
	[Vendor].[VendorID] as [Vendor_VendorID],
	[Vendor].[Name] as [Vendor_Name],
	[SubQuery2].[VendCnt_Name] as [VendCnt_Name],
	[SubQuery2].[Calculated_RowNumber] as [Calculated_RowNumber]
from Erp.Vendor as Vendor
left outer join  (select 
	[VendCnt].[VendorNum] as [VendCnt_VendorNum],
	[VendCnt].[Name] as [VendCnt_Name],
	(ROW_NUMBER() over (partition by VendCnt.VendorNum order by VendCnt.Name)) as [Calculated_RowNumber]
from Erp.VendCnt as VendCnt)  as SubQuery2 on 
	Vendor.VendorNum = SubQuery2.VendCnt_VendorNum
	and ( SubQuery2.Calculated_RowNumber < 2  )
1 Like
3

To explain.

Subquery 1

  • Vendor.VendorNum
  • Vendor.VendorID
  • Vendor.Name
  • SubQuery2.VendCnt_Name
  • SubQuery2.Calculated_RowNumber

Subquery 2

  • VendCnt.VendorNum
  • VendCnt.Name
  • (ROW_NUMBER() over (partition by VendCnt.VendorNum order by VendCnt.Name)) as Calculated_RowNumber

Left Outer Joined on Vendor.VendorNum → VendCnt.VendorNum

Condition on Subquery 1 → SubQuery2.Calculated_RowNumber < 2

1 Like
4

You’re my hero Klince! Thanks a bunch

1 Like
5

Thank @Banderson

2 Likes
6

I guess this is a tangent since the post says “first” record, but we just link on primary contact:

image
image701×386 18.4 KB

7

Maybe not.

Might find out that is actually what they want and didn’t think about it.