1

How can I Find in MS-SQL table values that is max on 3 columns, plus max on a single column.

I know to look at the three columns to get them, and I know to do a self join to get the single value - but how can I combine the two.

Normally when I add a new value to this I have all the other data and as I add new ones I have all the values handy. But for this special case I don't have this. I have the survey_id values and that is it. I need to find the next question ID for that survey, then find the last position in the survey. They may not be the same thing.

I'll but doing this is vb.net, not that it makes any difference.

I need to find for each survey_id the highest question_id AND the highest chapter, subchapter, question_number - that is chapter 2, sub 1, question 1 is greater than chapter 1, sub 99, question 99.

given a table that looks like this (survey_id and question_id form unique pair)

survey_id | question_id | chapter | subchapter | question_number
================================================================
505       | 1           |  1      |    1       |     1
505       | 2           |  1      |    1       |     3
505       | 3           |  1      |    1       |     2
5858      | 1           |  1      |    1       |     1
5858      | 2           |  1      |    1       |     2   
5858      | 3           |  1      |    1       |     2   
5858      | 47          |  1      |    1       |     4  
5858      | 45          |  2      |    1       |     1
5858      | 46          |  2      |    1       |     2
6060      | 1           |  1      |    1       |     1
6060      | 2           |  1      |    1       |     2   
6060      | 3           |  1      |    1       |     2   
6060      | 47          |  1      |    1       |     4  
6060      | 45          |  2      |    1       |     1
6060      | 46          |  2      |    1       |     2

My result should be 

survey_id | suveyMAXquestion_id | Maxchapter | Maxsubchapter | Maxquestion_number
=================================================================================
505       | 2                   |  1         |    1          |     3
5858      | 47                  |  2         |    1          |     2  
6060      | 47                  |  2         |    1          |     2

What I will end up doing is putting a new value into the table with survey_id, question_id +1 and chapter,subchapter, question_number+1

My data the will be insert in the table will (after updating the other columns in table that I have not shown) be: 

survey_id | question_id | chapter | subchapter | question_number
=================================================================
505       | 3           |  1      |    1       |     4
5858      | 48          |  2      |    1       |     3  
6060      | 48          |  2      |    1       |     3
Improve this question

3 Answers 3

Reset to default
2

This is a little bit complicated, but do-able. Since you mentioned that you have the survey_id, I included that as a parameter. There are two separate thoughts in what you are trying to do. First, you want the max question_id for the survey_id. The other thought is that you want the greatest question number for the highest chapter and subchapter listed. This must be found in order. First, we need the greatest chapter, then the greatest subchapter, and lastly the max question_number. 

Select q.survey_id, (Select max(question_id) from tblQuestion where survey_id=q.survey_id), q.chapter, q.subchapter, max(q.question_number)
From    tblQuestion q
Where   q.survey_id = @survey_id
and     q.chapter = (Select max(chapter)
                     From tblQuestion qq
                     Where  qq.survey_id=q.survey_id)
and     q.subchapter = (Select max(subchapter)
                        From tblQuestion qq
                        Where qq.survey_id=q.survey_id
                        and     qq.chapter = q.chapter)
Group by q.survey_id, q.chapter, q.subchapter

SQLFiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The only other thing I would mention is that you may want to replace some of the where clauses in the subselects to join on the parameter as opposed to the q.survey_id. It could improve performance by changing the execution plan, depending on if chapter and subchapter are in your indexes.
My needs don't require a lot of performance, that is to say I can slow down the database/server without causing issues for other users.
2

You can get both using Windowed Aggregate Functions without additional join:

SELECT survey_id, max_question_id, chapter, subchapter, question_number
FROM
 (
   SELECT survey_id, chapter, subchapter, question_number, 
          MAX(question_id)
          OVER (PARTITION BY survey_id) AS max_question_id,
          ROW_NUMBER()
          OVER (PARTITION BY survey_id
                ORDER BY chapter DESC, subchapter DESC, question_number DESC) AS rnk
   FROM tblquestion
 ) AS dt 
WHERE rnk = 1
Improve this answer

1 Comment

I've never seen the OVER (or partition) used before, pretty cool.
0

Since you are using SQL Server, using a CTE will allow you to do what you want.

with cte (survey_id, chapter, subchapter, question_number, rank)
as
(
  select survey_id, chapter, subchapter, question_number, 
          rank() over (partition by survey_id order by chapter desc, subchapter desc, question_number desc)
  from tblquestion
)

select q.survey_id, max(q.question_id) as question_id, c.chapter, c.subchapter, c.question_number
from cte c
inner join tblQuestion q on q.Survey_id = c.survey_id
where c.rank = 1
group by q.survey_id, c.chapter, c.subchapter, c.question_number
Improve this answer

1 Comment

I don't understand how I would use this. Never have seen CTE before.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.