CDS returns different result set if I query with filter

[joao_sousa2]

I have the following CDS view: 

define view entity ZCDSX_TEST as select from I_Equipment as e
left outer to one join I_SubscrpnContrItmRefObj as _refObjects on e.Equipment = _refObjects.SubscrpnContrItmRefObjEquip
{
    key e.Equipment as Equipment,   
    max(_refObjects.SubscriptionContract ) as ContractId,
    max(_refObjects.SubscriptionContractItem) as ContractItemId
}
group by e.Equipment, _refObjects.SubscriptionContract, _refObjects.SubscriptionContractItem

Where I can have more then one entry on the left outer join. I only want one entry which is why I'm using the max and group by. 

Let's say i query the whole set, I get 1 entry for equipment 1000001 despite the fact there are 10 entries for 100001 on the second table. 

Now if i do a query by primary key, inserting 1000001 in the "where" clause I get all 10 entries returned. 

Why do i have this behavior, with different results depending on the where clause? I can see this behavior both in SE16N, OData or ADT Data preview. The moment I set a filter for any column, the result set becomes different (the filter doesn't have to be on primary key, a filter on another column does the same thing).

Just as a note, I'm a newbie on this, this is more of a side project, just trying to understand CDS.

[MKreitlein]

Hello @joao_sousa2 

this is a common mistake, I did as well in the beginning 🙂

MAX works only for key figures. But you want to get the highest number in a characteristic, right?

For this you need to work with RANK.

See here some examples:

https://community.sap.com/t5/technology-blogs-by-members/about-row-number-rank-and-dense-rank-functi...

BR, Martin

[joao_sousa2]

Hello,

It's not exactly the max value I want, i just want one of them. The thing in this data model is that although the general relation is 1:N, through configuration you can say it's 1:1. The issue is some entries were generated without this configuration being in place, so I have some garbage records with 1:N which i would like to ignore. It's one of those "trust me, the select single works despite the primary key not being complete". 

I would like a "DISTINCT" based on the equipmentID primary key but it doesn't seem i can do that using CDS. The left outer to one join doesn't actually enforce the single join from what I understand. 

Thanks!