0
StudentId  QuestionId  Incorrect  Unanswered Flagged  pace  id  SubtopicId
==========================================================================
1          1           1          0          0        2     1   1
1          1           0          0          0        4     2   1
1          2           0          0          0        8     3   1

I have this table with the above values in it. I want to select all the the questions attempted by a student for a particular subtopic only once. In case there are multiple entries for a question i want to select the one with the greater id. I couldn't figure out a way to select the question with the greater id. This is what i am trying. 

SELECT QuestionId
     , pace
     , id 
  From table 
 where StudentId = 1 
   and SubtopicId = 1 
 Group 
    By QuestionId 
 Order 
    BY id desc;

Can anyone please tell me the modifications in my query or suggest a new one?

Tanner
  • 22,205
  • 9
  • 65
  • 83
Ali Ahmad
  • 11
  • 5
  • `my-sql` is not the same as `sql-server`... please remove the extra tag. – Tanner Jul 26 '17 at 09:05
  • what is the expected answer for your data? – Radim Bača Jul 26 '17 at 09:12
  • SELECT * From table t1 where not exists ( SELECT * From table t2 where t2.StudentId = t1.StudentId and t2.SubtopicId = t1.SubtopicId and t2.QuestionId = t1.QuestionId and t2.id > t1.id ) where StudentId = 1 and SubtopicId = 1 – Radim Bača Jul 26 '17 at 09:21

1 Answers1

0

Can you please try:

SELECT QuestionId, MAX(id) AS MaxId
FROM table
WHERE StudentId = 1
  AND SubtopicId = 1
GROUP BY QuestionId
ORDER BY id DESC
KtX2SkD
  • 752
  • 4
  • 12