SAP CAP CDS Distinct does not return distinct values

[Animatron56]

I am currently trying to implement a distinct entity projection based on one of my tables. The table in the data base is pretty simple and holds onto users in differnt sap onprem systems: 

entity SAPUsers: cuid {
 userId: String; 
userName: String; 
email: String;
}

In my CAP project I want to provide a search field so an enduser can search across all our SAP Landscape for one user. The user simply has to type in the search field the users username, id oder email and the SAP User will be shown in the field. 

Unfortunately across our Landscape, the username and email is not always the same (but the ID is always the same). So it can happen that one user has multiple entries in the table but the same user id. 

To make my search field a little bit more beautiful I would like to show the distinct user IDs based on the first entry that is found. 

Therefore I implemented a projection looking like the following

    entity DistinctUsers              as
        select from SAPUsers distinct{
            key userId,
            email,
            userName
        };

Since I did not find any documentation about the cds distinct keyword in the SAP CAP docu expect the following question: Question Distinct my assumption was that the distinct would be based on the id field, since this is the key for this projection. 

Unfortunately when I create the distinction like that, users that have different email or username will be shown multiple times. I also cannot leave out the email or username in the distinct entity since I need those in the search field. I also tried out to put the distinct next to the select and create a SQL like querry, but none of it worked. 

Therefore I am a little stuck on how to create a distinct entity based on the ID field but also have access to the email and userName.  

[catano]

Hi, Could you write a simple example of what data should be returned in DistinctUsers for a set of SAPUsers (userId, email, userName)?

[Animatron56]

Hello @catano,

In the SAPUsers there is the following data: 

[
{
 userId: "Test1"; 
userName: "Test User"; 
email: "test@user.de";
},
{
 userId: "Test1"; 
userName: "Tääst User"; 
email: "täst@user.de";
},
{
 userId: "Test1"; 
userName: "Tessst User"; 
email: "tessst@user.de";
},
{
 userId: "Test1"; 
userName: "Teßt User"; 
email: "teßt@user.de";
},
]

As you can see in my example, for the user ID Test1, there are 4 different entries in SAPUsers since the user was manually created and they made some typos or didnt know how to correctly write the name / email. 

In my DistinctUsers view I only want the first entry that is available for this user id to be shown. So I want to have distinct values by the ID (i.e. Test1) and the first username and email that is found in the database. Like the following:  

[
{
 userId: "Test1", 
userName: "Test User", 
email: "test@user.de"
}
]

[catano · Accepted solution]

Hi @Animatron56 ,

Could you try if the following works for you?

entity DistinctUsers as
    select
        ID,
        userId,
        userName,
        email
    from (
        select
            ID,
            userId,
            userName,
            email,
            row_number() over(
                partition by userId order by ID
            ) as rn
        from SAPUsers
    )
    where
        rn = 1;

Best,

Peter