A. Gordon A. Gordon - 2 months ago 16
SQL Question

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.

vkp vkp
Answer

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