Query to obtain the maximum primary key ID of a table for each unique value of the customerID column in the same table

I’m trying to run a query that takes obtains the maximum QuestionnaireId value for each unique value in column VendorId.

So for example from this table:

QuestionnaireId  VendorId
1                10003
2                10004
3                10004
4                10006
5                10005
6                10007
7                10005
8                10005

I would obtain:

QuestionnaireId  VendorId
1                10003
3                10004
8                10005
4                10006
6                10007

I’m using the following code to get the maximum QuestionnaireId, but need another statement alongside it to get the unique VendorIds as well. Note that the statement I’ve included is just last segment of a large Join function to combine all of my tables into one.

WHERE Questionnaire.QuestionnaireId = (SELECT MAX(Questionnaire.QuestionnaireId) FROM Questionnaire)

Answer

Just use aggregation:

select max(q.QuestionnaireId) as QuestionnaireId, VendorId
from Questionnaire
group by VendorId;