Some users has test and users could take a test N times. And I want to select last answers to the questions.

I have table results:

| Id  | userId  | answer_Id | question_Id |
|:--- | :-----: | :--------:| ----------: |
| 1   |  1      | 2         |  1          |
| 2   |  1      | 3         |  2          |
| 3   |  1      | 5         |  1          |
| 4   |  1      | 7         |  2          |
| n   |  n      | n         |  n          |

Normally I can easily figure it out but you know smt you brain is stop working and today is not my day.

CodePudding user response：

you can use max() function to get the last answer for a given question for each users.

select max(answer_id), userId
from table1 where question_id = ?
group by userId

CodePudding user response：

This would be the solution I would consider using:

You can replace the 't.question_id', in line 3, with the question_id that you need the answers from

Select * From test t 
Where answer_id = 
(Select Max(answer_id) From test Where userid = t.userid AND question_id=t.question_id) 
ORDER BY t.question_id ASC 

CodePudding user response：

You can achieve this using ROW_NUMBER() function like this:

WITH CTE AS (
  SELECT Id, userid, question_id,
  ROW_NUMBER() OVER(PARTITION BY userid,question_id ORDER BY Id DESC) AS RN
  FROM table1
)
SELECT t1.* FROM table1 t1
JOIN CTE ON t1.ID = CTE.ID AND CTE.RN=1

Output:

See this db<>fiddle