CODEWITHSUNDEEP
sqlpostgresql
user007
user007

Reputation: 1084

SQL incremental id for every user_id

I have data:

user_id  user_login_date
1        2013.07.05 
1        2013.07.15
1        2013.07.16
1        2013.07.17
2        2013.07.05
2        2013.07.05
2        2013.07.15

And I want to make virtual table that would look like this:

user_id  user_login_date date_id
1        2013.07.05      1
1        2013.07.15      2
1        2013.07.16      3
1        2013.07.17      4
2        2013.07.05      1
2        2013.07.05      2
2        2013.07.15      3

How do I do that? I tried:

WITH user_count
AS (
  SELECT user_id, user_login_date
  FROM users
)
SELECT user_count.user_id, user_count.user_login_date, COUNT(user_count.user_id)
FROM users, user_count
WHERE users.user_login_date >= user_count.user_login_date
AND users.user_id = user_count.user_id
GROUP BY user_count.user_id, user_count.user_login_date
ORDER BY user_count.user_id, user_count.user_login_date;

But the result isn't that that I want.

Upvotes: 0

Views: 59

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125434

select
    user_id, user_login_date,
    row_number() over(
        partition by user_id
        order by user_login_date
    ) as date_id
from users
order by user_id, date_id

Upvotes: 2

Andomar
Andomar

Reputation: 238196

select  row_number() over (partition by user_id order by user_login_date) as date_id
,       yt.*
from    YourTable yt

Upvotes: 0

Related Questions