Reputation: 476
I have a data set with a test table, a question table and an response table. Some of the questions can be answered multiple times by the same user. Each time a user answers the same question multiple times, the order they answer in is stored as a sort order on the answer.
I'm trying to write a query that will return the information I want for each answer given the test id, as well as the number of times a user answered each question. I'll use that number with the sort order to specific "answer n of 3", where n is the sort order number and 3 is the number of times the user answered that question. The sql I have thus far is, which does not give the number of times answered, is:
select q.id as question_id
,r."textValue" as text_value, r.id, r.id as response_id
,q."internalQuestion" as "is_internal_question"
,q."requestExplanation" as "question_text"
,r."subResponseOrder" as "subOrder"
from response r
join question q on (r.question_id = q.id)
where r.test_id = 12345
How do I modify this query to also give me the max suborder of each of the questions submitted by the user? As in user 3 answer question #3 four times and question #2 two times. The number should be on each of the answers. In other words each of user 2's answers to question 3 would have a "number_of_responses" number of 4. Paired with the subOrder, I could say that an answer was 1 of 4, 2 of 4, etc... I'm thinking it would be a sub query, or a max, but I've not been able to get a max to work, and I have no idea of the best way to achieve it.
Upvotes: 1
Views: 35
Reputation: 1271241
If I understand correctly, you can use window functions:
select q.id as question_id,
r."textValue" as text_value, r.id, r.id as response_id,
q."internalQuestion" as "is_internal_question",
q."requestExplanation" as "question_text",
r."subResponseOrder" as "subOrder",
max(r."subResponseOrder") over (partition by q.id) as max_subResponseOrder
from response r join
question q
on r.question_id = q.id
where r.test_id = 12345
Upvotes: 2