Forum Discussion
Helper IHow to retrieve the highest ranked value from another table
Hi all,
Following on from an earlier query, I need to understand how to return a value from another table based on its rank. These are text based values, and there are about 10 possible different values.
BACKGROUND
I have a bunch of session data in Tabe 1, and I have a bunch of user data in Table 2. A user will have typically have played many sessions, hence there will be many rows per user in the session table.
I am trying to add a column to table 2, based on a look up of data in table 1, which I can do fine by matching id's on both tables. However I need to retrieve the highest ranked value for each user in Table 1.
Thanks in advance
Kevin
16 Replies
- vanessafvg
Community Champion
what is the value of the highest rank based on the most sessions? please can you post some sample data
ksfx77Hi vanessafvg,
It would be based on most occurrences. So in the very simple example below 'Platform 2' and 'Platform 3' would have a count of one, and 'Platform 1' has a count of 2, so the text 'Platform 1' would be returned.
TABLE 1
date, platform, uniqueId
10/03/2021, platform 1, id1
10/03/2021, platform 2, id1
10/03/2021, platform1, id1
10/03/2021, platform 3, id1
I hope this helps?Kevin
- vanessafvg
it does help but you have different id per platform is that what you intended? because then platform 1 would be returned to table a at the user level, but that user didn't watch it 2 x
