CODEWITHSUNDEEP
sqlpostgresql
Travis Stevens
Travis Stevens

Reputation: 2238

Join with indices (not database index) of right table

I have a one to many relationship and want to join the right table while including an index number for each of the rows on the many table.

For instance:

user table

id | name
 1 | me
 2 | you

attempted table
user_id | date 
      1 | 2014-03-01
      1 | 2014-03-03
      1 | 2014-03-02
      2 | 2014-02-01

join result
user_name | date       | attempt_number
       me | 2014-03-01 | 0     
       me | 2014-03-02 | 1
       me | 2014-03-03 | 2
      you | 2014-02-01 | 0

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You can use row_number() for the enumeration.

select u.name, a.date,
       (row_number() over (partition by u.name order by a.date) - 1) as attempt_number
from user u left outer join
     attempted a
     on a.user_id = u.id;

EDIT:

I struggle with the type of join here. The sample data only has one user name in it, "me", yet the sample output has two, "me" and "you". The attempted table references two user ids.

I am assuming that the relevant tables are just truncated for the question, and that the user ids in attempted have a proper foreign key relationship to the user table. Hence, this query uses a left outer join, which keeps all the rows from the user table. It could also just use an inner join.

Upvotes: 1

Related Questions