How to return the max row based on one column in join query?

Question

I have a query that returns a result set with some IDs from a few tables. If a get two or more rows with the same QuestionID I only want the one with MAX(QuestionSessionID). How can I achieve this?

I have tried a bunch of different variations with subqueries but with no success. How to achieve this?

The query:

SELECT AQS.QuestionSessionID, AQS.QuestionID, AQS.AnswerTextMarkerID, AQS.Correct, QG.ID AS QuestionGroupID
FROM AnswerQuestionSession AQS
JOIN QuestionSession QS ON QS.UserID = 3 
JOIN Question Q ON AQS.QuestionID = Q.ID
JOIN QuestionGroup QG ON Q.QuestionGroupID = QG.ID
WHERE AQS.AnswerTextMarkerID IN (109,110,113,114,118,121,141,146,148,152,156,157,158,172,182,183,193,194,196,197,198,211,222,227,241,242,243,257,263,271,282,283,356,396,643,644,938,939,943,944,955,956,957,958,959,970,971,972,973,978,979,1110,1111,1112,1113,1114,1115,1116,1117,1118,1120,1121,1163,1164,1165,1166,1205,1240)
AND AQS.QuestionSessionID = QS.ID
ORDER BY AQS.QuestionID, AQS.QuestionSessionID DESC;

Current result set:

QuestionSessionID QuestionID AnswerTextMarkerID Correct QuestionGroupID
294441            112        121                1       25
22942             112        121                0       25
22942             126        141                1       39
131489            216        257                1       102
22942             222        263                1       106
22942             227        271                1       110
294435            760        955                1       5
294435            760        956                1       5

So, in the above example I only want one of the rows with QuestionID 112 (the one with MAX(QuestionSessionID) 294441), like this:

Desired result set:

QuestionSessionID QuestionID AnswerTextMarkerID Correct QuestionGroupID
294441            112        121                1       25
22942             126        141                1       39
131489            216        257                1       102
22942             222        263                1       106
22942             227        271                1       110
294435            760        955                1       5
294435            760        956                1       5

UPDATE: Tried adding another join as suggested by a commenter, but didn't get it right. It seems to work only on rows with more than one of the same QuestionID:

SELECT AQS.QuestionSessionID, AQS.QuestionID, AQS.AnswerTextMarkerID, AQS.Correct, QG.ID AS QuestionGroupID, MaxId
FROM AnswerQuestionSession AQS
JOIN QuestionSession QS ON QS.UserID = 3 
JOIN Question Q ON AQS.QuestionID = Q.ID
JOIN QuestionGroup QG ON Q.QuestionGroupID = QG.ID
JOIN (SELECT QuestionID, MAX(QuestionSessionID) as MaxId
      FROM AnswerQuestionSession
      GROUP BY QuestionID) as mq ON mq.QuestionID = AQS.QuestionID
WHERE AQS.AnswerTextMarkerID IN (109,110,113,114,118,121,141,146,148,152,156,157,158,172,182,183,193,194,196,197,198,211,222,227,241,242,243,257,263,271,282,283,356,396,643,644,938,939,943,944,955,956,957,958,959,970,971,972,973,978,979,1110,1111,1112,1113,1114,1115,1116,1117,1118,1120,1121,1163,1164,1165,1166,1205,1240)
AND AQS.QuestionSessionID = QS.ID
/*AND AQS.QuestionSessionID = MaxId*/
ORDER BY AQS.QuestionID, AQS.QuestionSessionID DESC;

QuestionSessionID QuestionID AnswerTextMarkerID Correct QuestionGroupID MaxId
294441            112        121                1       25            294441
22942             112        121                0       25            294441
22942             126        141                1       39            293891
131489            216        257                1       102           294071
22942             222        263                1       106           294013
22942             227        271                1       110           294013
294435            760        958                1       5             294435
294435            760        959                1       5             294435
294435            760        955                1       5             294435
294435            760        956                1       5             294435
294435            760        957                1       5             294435
294435            771        970                1       241           294435
294435            771        971                1       241           294435
294435            771        972                1       241           294435
294435            776        978                1       245           294435
131489            962        1205               1       318           293592

UPDATE 2:

I got it to work as expected with a small modification based on help from a commenter:

WORKING QUERY:

SELECT AQS.QuestionSessionID, AQS.QuestionID, AQS.AnswerTextMarkerID, AQS.Correct, QG.ID AS QuestionGroupID
FROM AnswerQuestionSession AQS
JOIN QuestionSession QS ON  AQS.QuestionSessionID = QS.ID 
JOIN Question Q ON AQS.QuestionID = Q.ID
JOIN QuestionGroup QG ON Q.QuestionGroupID = QG.ID
JOIN (SELECT QuestionID, MAX(QuestionSessionID) as MaxId
      FROM AnswerQuestionSession AQS2
      JOIN QuestionSession QS2 ON AQS2.QuestionSessionID = QS2.ID
      WHERE QS2.UserID = 3
      GROUP BY QuestionID) as mq ON mq.QuestionID = AQS.QuestionID
WHERE AQS.AnswerTextMarkerID IN (109,110,113,114,118,121,141,146,148,152,156,157,158,172,182,183,193,194,196,197,198,211,222,227,241,242,243,257,263,271,282,283,356,396,643,644,938,939,943,944,955,956,957,958,959,970,971,972,973,978,979,1110,1111,1112,1113,1114,1115,1116,1117,1118,1120,1121,1163,1164,1165,1166,1205,1240)
AND QS.UserID = 3 
AND AQS.QuestionSessionID = MaxId;

Joakim Danielson · Accepted Answer · 2019-01-22T07:52:20.397

Add a JOIN against a subquery on the AnswerQuestionSession table

JOIN (SELECT QuestionID, MAX(QuestionSessionID as MaxId)
      FROM AnswerQuestionSession
      GROUP BY QuestionID) as mq ON mq. QuestionID = Aqs. QuestionID

and then use it in the WHERE clause

AND Aqs.QuestionSessionID = MaxId

This is based on the updated question, I changed the join on QuestionSession and also made sure MaxId was used in the WHERE clause.

SELECT AQS.QuestionSessionID, AQS.QuestionID, AQS.AnswerTextMarkerID, AQS.Correct, QG.ID AS QuestionGroupID
FROM AnswerQuestionSession AQS
JOIN QuestionSession QS ON  AQS.QuestionSessionID = QS.ID
JOIN Question Q ON AQS.QuestionID = Q.ID
JOIN QuestionGroup QG ON Q.QuestionGroupID = QG.ID
JOIN (SELECT QuestionID, MAX(QuestionSessionID) as MaxId
      FROM AnswerQuestionSession
      GROUP BY QuestionID) as mq ON mq.QuestionID = AQS.QuestionID
WHERE AQS.AnswerTextMarkerID IN (109,110,113,114,118,121,141,146,148,152,156,157,158,172,182,183,193,194,196,197,198,211,222,227,241,242,243,257,263,271,282,283,356,396,643,644,938,939,943,944,955,956,957,958,959,970,971,972,973,978,979,1110,1111,1112,1113,1114,1115,1116,1117,1118,1120,1121,1163,1164,1165,1166,1205,1240)
AND QS.UserID = 3 
AND AQS.QuestionSessionID = MaxId
ORDER BY AQS.QuestionID, AQS.QuestionSessionID DESC;

@user9223839 thanks for your comment. Tried it but didn't work 100%, I updated the question with your suggestion and a new result set. Any ideas? — poa, Jan 21 '19 at 22:20
@user9223839 Updated the question with a working version (with a small modification, added another join to get the maxId) of the query based on this comment. Thank you! — poa, Jan 22 '19 at 11:00

How to return the max row based on one column in join query?

1 Answers1