CODEWITHSUNDEEP
sqlpostgresql
A. Gordon
A. Gordon

Reputation: 57

Window functions limited by value in separate column

I have a "responses" table in my postgres database that looks like 

| id | question_id |
| 1  | 1           |
| 2  | 2           |
| 3  | 1           |
| 4  | 2           |
| 5  | 2           |

I want to produce a table with the response and question id, as well as the id of the previous response with that same question id, as such

| id | question_id | lag_resp_id |
| 1  | 1           |             |
| 2  | 2           |             |
| 3  | 1           | 1           |
| 4  | 2           | 2           |
| 5  | 2           | 4           |

Obviously pulling "lag(responses.id) over (order by responses.id)" will pull the previous response id regardless of question_id. I attempted the below subquery, but I know it is wrong since I am basically making a table of all lag ids for each question id in the subquery. 

select
    responses.question_id,
    responses.id as response_id,
    (select
        lag(r2.id, 1) over (order by r2.id)
    from
        responses as r2
    where
        r2.question_id = responses.question_id
    )
from
    responses

I don't know if I'm on the right track with the subquery, or if I need to do something more advanced (which may involve "partition by", which I do not know how to use).

Any help would be hugely appreciated.

Upvotes: 0

Views: 23

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use partition by. There is no need for a correlated subquery here.

select id,question_id,
lag(id) over (partition by question_id order by id) lag_resp_id
from responses

Upvotes: 1

Related Questions