CODEWITHSUNDEEP
sqlpostgresql
Tyrel Denison
Tyrel Denison

Reputation: 476

SQL query to return the max of a column when the rows match on a different column

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions