Getting only unique records in this situation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting only unique records in this situation

Author

Topic

waveform
Yak Posting Veteran

93 Posts

Posted - 2010-12-01 : 01:53:12

Hi, I'm working on a fairly standard "survey" type application, in which there is a Questions table and a QuestionTypes table (which I'll just call "Types" for brevity). Question "types" are whether the question is a dropdown list, option list, text, etc. Obviously there can be multiple questions of the same type.

I want to pick out a distinct list of Types (ie. not repeating any) from the Types table, in the order in which they appear in the survey, ie. in question order.

My query is basically this:
SELECT t.TypeID, t.TypeTitle FROM QuestionTypes AS t INNER JOIN Questions AS q ON q.TypeID = t.TypeID ORDER BY q.QuestionNumber

Of course the above query will *repeat* the TypeID's for every question. I want the resultset to include only 1 of each type. If I use DISTINCT, SQL Server demands I include "q.QuestioNumber" in the SELECT list, which obviously will give me the same result - repeated types. Same if I use GROUP BY.

So how I pull out only 1 of each Type, while still ordering them by how they appear in the survey (ie. ordered by QuestionNumber)? Is there a way?

Many thanks!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-01 : 02:15:16

u can also use 'COUNT'.by using count you can just see how many questions are in each type.in the resultset you will get two columns.one will be 'question type' and other will be no of questions in each type.but if you want to show question number you will get repeated data.
if iam not wrong

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-01 : 02:23:47

Try this -


SELECT TypeID, TypeTitle FROM 
(
SELECT	t.TypeID, t.TypeTitle, ROW_NUMBER() OVER ( PARTITION BY t.TypeID, t.TypeTitle ORDER BY q.QuestionNumber ) RowNo 
FROM	QuestionTypes AS t 
		INNER JOIN Questions AS q ON q.TypeID = t.TypeID
) A 
WHERE RowNo = 1

Vaibhav T

If I cant go back, I want to go fast...

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-01 : 02:33:46

but he requires a column question number from the questions table also int result set.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-01 : 02:54:04

quote:
Originally posted by ahmeds08

but he requires a column question number from the questions table also int result set.

In Below query In Outer Select List you can have all columns from both the tables.
You can give list of columns whatever you want.


SELECT * FROM 
(
SELECT	* , ROW_NUMBER() OVER ( PARTITION BY t.TypeID, t.TypeTitle ORDER BY q.QuestionNumber ) RowNo 
FROM	QuestionTypes AS t 
		INNER JOIN Questions AS q ON q.TypeID = t.TypeID
) A 
WHERE RowNo = 1

waveform
Yak Posting Veteran

93 Posts

Posted - 2010-12-01 : 03:40:05

Hi, thanks for the resposnes - no, I don't need the questions numbers, I just want to get the TypeID and TypeTitle out of the QuestionTypes table, but in the *order* in which they are used in the survey - ie. ordered by Question number.

So if question 1 is a drop-down type, and question 2 is an option list, that's the order in which I want the Type information. Drop-down then option list. Hope that helps clarify.

Thanks Vaibhav, I'm not familiar with OVER, ROW_NUMBER and PARTITION, so I'll check that out.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-01 : 03:50:08

quote:
Originally posted by waveform

Hi, thanks for the resposnes - no, I don't need the questions numbers, I just want to get the TypeID and TypeTitle out of the QuestionTypes table, but in the *order* in which they are used in the survey - ie. ordered by Question number.

So if question 1 is a drop-down type, and question 2 is an option list, that's the order in which I want the Type information. Drop-down then option list. Hope that helps clarify.

Thanks Vaibhav, I'm not familiar with OVER, ROW_NUMBER and PARTITION, so I'll check that out.

So for your requirement My first Post was sufficient.

Vaibhav T

If I cant go back, I want to go fast...

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-01 : 03:56:38

perfect vaibhav...thanx

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-01 : 04:27:32