How can I get the recently score per user in SQL query?

Hi, I  have a question about sql:

I need to make a query where I can have several scores / stars from the same user, in this case my goal is to get the most recent score from the various users that is not repeated from the same user. How can I get this result with a query? I need a subquery right? 

I just want the most recent rating and only one per user, in the attachments I have two with the same user.



Rank: #67

Hi Rita,

You need to group your query by your {User}.[Id] and {User}.[Name] (or other user-related fields you need) and apply a MAX or SUM (or other aggregator depending on your needs) on the rating field.

If you want the most recent rating per user, you will need an advanced query with a subquery indeed.

Kind regards,

Rank: #14499

Hi João,

Thank you very much for your help.

Yes, I want the most recent rating per user, and use it in a table. My problem is how to do that in a subquery.


Rank: #67

Hi Rita,

I don't know exactly your data model but your query should look something like this:











              MAX({EvaluateStarsCandidate}.[CreatedDateTime]) MaxCreatedDate



       GROUP BY



       ON MaxDate.[CreatedByUserId] = {User}.[Id]



       ON {EvaluateStarsCandidate}.[CreatedDateTime] = MaxDate.[MaxCreatedDate]

              AND {EvaluateStarsCandidate}.[CreatedByUserId] = {User}.[Id]

The subquery MaxDate isolates the maximum created date for each user (the most recent) and then you use that date to join again with the table to get only the most recent record detail.

If you need to get the users with no comments then the INNER JOINs should be changed to LEFT JOINs.

It should be enough for you to adapt to your use case and data model.


Rank: #14499

Wow much more complex than i had thought, i didn't know it was possible to use MAX on dates to get the most recent date, no doubt i still have to study a lot. 

Thank you very, very much, I don't know how to thank you.

All the best, a lot of success !!