13
Views
4
Comments
 How can I get the recently score per user in SQL query?
Question

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.

Thanks

jK4DL.png

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,
João

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.

Rita

Rank: #67

Hi Rita,


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


SELECT

       {User}.[Id],

       {User}.[Name],

       {EvaluateStarsCandidate}.[EvaluateStarId]

FROM

       {User}

       INNER JOIN

       (

       SELECT

              {EvaluateStarsCandidate}.[CreatedByUserId],

              MAX({EvaluateStarsCandidate}.[CreatedDateTime]) MaxCreatedDate

       FROM

              {EvaluateStarsCandidate}

       GROUP BY

              {EvaluateStarsCandidate}.[CreatedByUserId]

       )MaxDate

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

       INNER JOIN

       {EvaluateStarsCandidate}

       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.


Regards,
João

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 !!