SQL Server Group by clause - Simple stuff

Question

I have a table with questions and answers and sessionid.

Sometimes the same person (sessionid) will answer the same question more than 1 time, and that gets stored in the table.

The qapp_answer table content looks something like this:

Id, SessionID, QNumber, Qanswer
72     11         1       3
73     11         1       4
74     11         2       1
75     11         2       3
76     11         3       1

So, I only want each Qnumber to be displayed one time (so 3 rows in total), and basically just use their latest answer for display (Qanswer).

This is the code so far:

select Qnumber, Qanswer 
from qapp_answers
where sessionid = 11
group by QNumber, Qanswer

And it returns 5 rows.

Should be simple, but i havent used SQL for years.

Answer 1

You can basically use ROW_NUMBER() which generates sequential number based on the group specified. The query belows group the records by QNumber and generates sequential number sorted by ID in descending order. The latest ID for every group has the value of 1 so you need to filter out records that has a generated value of 1.

SELECT ID, SessionID, QNumber, Qanswer
FROM
    (
        SELECT  ID, SessionID, QNumber, Qanswer,
                ROW_NUMBER() OVER (PARTITION BY QNumber ORDER BY ID Desc) rn
        FROM    tableName
        WHERE   SessionID = 11
    ) a
WHERE   a.rn = 1

• SQLFiddle Demo