How to get SO and group by user_id and last created

I am trying to get the user and operating system data but a list of data but grouping it by the last creation date.

This is my data examples.

My SQL is:

SELECT pn.os,
       pn.user_id,
       Max(pn.created_at) AS created_at
FROM   pp_notac AS pn
WHERE  pn.user_id IN ( 50, 54, 37 )
GROUP  BY pn.user_id,pn.os;

By result is:

But what I want is to return me for example from user 50 the last one created and add according to the operating system leaving the result in this way.

The result expected:

ios -> 2
android -> 1

How to change the SQL for get the expected result?

CodePudding user response：

You never included the output you expect, but I suspect this is the query you need. Conceptually, it appears that you want the latest record for each user. If so, then conceptually you should not be aggregating by user and OS, but rather only by user. However, in the query below, we use ROW_NUMBER to obtain the result you want.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
    FROM pp_notac
    WHERE user_id IN (50, 54, 37)
)

SELECT os, user_id, created_at
FROM cte
WHERE rn = 1;