How can I fetch 'distinct' data keeping row ID in the lookup view?

Question

Assume I have an entity with the following structure:

ID (it would be the GUID of the record, I'll use number in this example for easier read)
Name
Product (lookup to product)
Price List (custom lookup to standard price list) 

I now need to build a lookup view in this fashion:

(Sample data) 
ID    Name    Product    PriceList
-----------------------------------
1      Rec1    P1         PL1
2      Rec1    P1         PL2 
3      Rec1    P1         PL3
4      Rec2    P2         PL1
5      Rec2    P2         PL2

(Desired result in lookup view)
ID    Name
-----------------------------------
1     Rec1              
4     Rec2              

To explain the requirement in plain english, it is:

In the lookup view I only want one row per Name: Product and Price List don't matter.

Here's what I'd do if I could use straight T-SQL:

;WITH cte AS
(
    SELECT ROW_NUMBER() 
    OVER (PARTITION BY Name ORDER BY Name ASC) AS RowNo, ID, Name
    FROM FilteredEntity
)
SELECT ID, Name FROM cte WHERE RowNo = 1

But I can't figure out how to achieve the same result set in a FetchXML query.

Answer 1

The only way I can think of to do this would go like this:

1. In JS on your form, determine the unique names (Rec1 and Rec2 from your example), and store the ids of two records that reference Rec1 and Rec2 (let's call these ids Id1 and Id2)
2. Generate a fetch query that returns only those two records. Your fetch query would specifically say show me records where id is Id1 or Id2.
3. Filter the lookup using addCustomFilter and addPreSearch (link).

Answer 2

I can see two approaches here, neither of which are pure FetchXML queries.

1. Use distinct, (I'm not 100% on this but worth giving a go). This will then hopefully get you a record for every name, and you just grab the first record of each in code.

<attribute name='name' distinct='true'/>

2. Perform the filtering client side using Linq.