Last value in list sql server 2008

Question

instead of

     MAX(survey_questions.question_type) as question_type 

I would like something like

     LAST (survey_questions.survey_id ) survey_questions.question_type as question_type

My table survey_questions has list of questions for various surveys. Higher the survey_id the more recent the question - we keep a list of the wording of the question for various iterations of the survey, sometimes the type of question changes (drop down, text, etc). The problem comes in my 'view' created this way

  select report_survey_questions.report_id,
         report_survey_questions.report_question_number,
         MAX(survey_questions.question_type) as question_type,
         MAX(CAST(survey_questions.client_provided_data_id as int)) as client_provided_data_id
  from report_survey_questions,
         survey_questions
  where report_survey_questions.survey_id = survey_questions.survey_id
         and  report_survey_questions.question_id = survey_questions.question_id
  group by report_survey_questions.report_id,
         report_survey_questions.report_question_number

So how can I instead of MAX get last (ie the highest survey_id), maintaining all the other parts.

Answer 1

Assuming that the "Last" / "Highest" survey_id is per question_id, the following should work. Or if the grouping is based on another field (question_type perhaps?), just change the field name in the PARTITION BY clause.

;WITH cte AS
(
  SELECT sq.question_type,
         sq.client_provided_data_id,
         sq.survey_id,
         sq.question_id,
         ROW_NUMBER() OVER (PARTITION BY sq.question_id ORDER BY sq.survey_id DESC)
               AS [RowNum]
  FROM   survey_questions sq
)
SELECT rsq.report_id,
       rsq.report_question_number,
       cte.question_type,
       CAST(cte.client_provided_data_id AS INT) AS [client_provided_data_id]
FROM   report_survey_questions rsq
INNER JOIN cte
        ON cte.survey_id = rsq.survey_id
       AND cte.question_id = rsq.question_id
       AND cte.[RowNum] = 1;