CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Robert D.M
Robert D.M

Reputation: 125

how to get the first entry from a distinct group by in postgresql

i want to find out the first entry of a user who signed up to my product (with a id) with his anonymous_id and a timestamp.

Since i know that a user , who already signed up & visit the page again, can have multiple anonymous_id (f.e using multiple devices, having new cookies etc...) , i distinct the user_id

i write a code who looks like this

SELECT distinct user_id , min(timestamp),anonymous_id
FROM data
group by 1,3

but now he gives me every first mention of the user with all anonymous_id

user_id | timestamp                   | anonymous_id
 ------ | ----------------------------|-------------
 12     |  2016-07-28 16:19:57.101+00 | x-1
 ------ | ----------------------------|-------------
 12     | 2016-08-24 09:17:21.294+00    y-23 
 12     | 2016-07-27 12:03:25.572+00    y-2345

i want only see the first mention of user_id 12 - in this case the one with the timestamp 2016-07-27 12:03:25.572+00

how i write the code so i get the first mention of the user_id?

Upvotes: 1

Views: 113

Answers (2)

user330315
user330315

Reputation:

The fastest way in Postgres is to use its proprietary distinct on ()

SELECT distinct on (user_id) user_id , timestamp, anonymous_id
FROM data
order by user_id, timestamp;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311308

You can use the row_number() window function:

SELECT user_id, timestamp, anonymous_id
FROM   (SELECT user_id, timestamp, anonymous_id,
               ROW_NUMBER() OVER (PARTITION BY user_id
                                  ORDER BY timestamp ASC) AS rn
        FROM   data) t
WHERE  rn = 1

Upvotes: 0

Related Questions