SQL - MAX function returning incorrect data

Question

I am trying to get with each question that having max seq_no record, but it is returning wrong data.

Here is query:

SELECT id, attempt, seq_number, question
FROM question_states
WHERE attempt = 374
AND seq_number IN ( SELECT MAX(seq_number) FROM question_states WHERE attempt = 374 GROUP BY question)
GROUP BY question, id

I have one table name as questions, structure is given below:-

id--seq_number--attempt--question
1 --0     --374  -- 130
2 --0     --374  -- 130
3 --1     --374  -- 130
4 --2     --374  -- 130
5 --1     --374  -- 131

Why this query returns 2 records against question 130 ? and if I remove the GROUP BY id then returns wrong data.

Answer 1

Try this:

SELECT id, attempt, seq_number, question
FROM question_states qs 
INNER JOIN (SELECT question, MAX(seq_number) seq_number 
            FROM question_states WHERE attempt = 374 GROUP BY question
           ) AS A ON qs.question = A.question AND qs.seq_number = A.seq_number 
WHERE attempt = 374

OR

SELECT id, attempt, seq_number, question
FROM (SELECT id, attempt, seq_number, question
      FROM question_states WHERE attempt = 374
      ORDER BY question, seq_number DESC
     ) AS A 
GROUP BY question