call a column, but don't want to group by

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

call a column, but don't want to group by

Author

Topic

nfam
Starting Member

3 Posts

Posted - 2011-01-17 : 01:29:16

hi,
i have a query to get maximum value with group by funtion

SELECT surveyID, questionID, choice, question, MAX(percentage) AS percentage
FROM dbo.vw_surveyAnsMatrixPercMax
WHERE (question LIKE 'workload')
GROUP BY surveyID, questionID, question, choice

the result of above query:
surveyID questionID choice question percentage
1 21 1 Workload 50.000000
1 21 2 Workload 33.333333
1 21 3 Workload 16.666666
1 47 1 Workload 100.000000

but i only want to get the highest data per group.
and i don't want to group by choice, but still want to display choice data. so that the result be:

surveyID questionID choice question percentage
1 21 1 Workload 50.000000
1 47 1 Workload 100.000000

is it possible?

thanks in advance..

Sachin.Nand

2937 Posts

Posted - 2011-01-17 : 05:10:21

quote:
i don't want to group by choice.is it possible?

Yes

quote:
i don't want to group by choice

Why ?

nfam
Starting Member

3 Posts

Posted - 2011-01-17 : 05:17:31

may i know how?

the problem is, if don't put 'choice' in GROUP BY clause,
it will give error:

Sql Execution Error.
Error message: column 'dbo.vw_surveyAnsMatrixPerc.choice' is invalid in the select list because it is not contained in either
an aggregate function or the GROUP BY clause.

I want to display choice, but not group by choice

thanks in advance

Sachin.Nand

2937 Posts

Posted - 2011-01-17 : 08:33:49

[code]
select * from
(
select surveyID,
questionID,
choice,
question,
row_number()over(partition by percentage order by questionID )rid
from dbo.vw_surveyAnsMatrixPercMax

)t where rid=1
[/code]

PBUH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-17 : 08:56:49

quote:

Originally posted by Sachin.Nand


select * from
(
	select surveyID,
	       questionID,
	       choice, 
	       question,
	       row_number()over(partition by surveyid, questionID order by percentage desc)rid
	from dbo.vw_surveyAnsMatrixPercMax

)t where rid=1

PBUH

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2011-01-17 : 23:46:24

Thanks for the correction Peso.I had totally screwed it up.

PBUH

nfam
Starting Member

3 Posts

Posted - 2011-01-18 : 00:07:14

thanks for your reply, unfortunately it hasn't resolve my problem.
sorry if i didn't explain clear enough.

say this is my data:
surveyID questionID question percentage choice
1 21 Access to company training 16.666666 2
1 21 Access to company training 16.666666 3
1 21 Access to company training 66.666666 1
1 21 Benefits 16.666666 2
1 21 Benefits 16.666666 3
1 21 Benefits 66.666666 1
1 21 Career advancement 16.666666 3
1 21 Career advancement 33.333333 2
1 21 Career advancement 50.000000 1
1 21 Pay and performance 16.666666 3
1 21 Pay and performance 33.333333 2
1 21 Pay and performance 50.000000 1
1 21 Flexibility of work hours 16.666666 2
1 21 Flexibility of work hours 16.666666 3
1 21 Flexibility of work hours 66.666666 1
1 21 Job security 16.666666 2
1 21 Job security 33.333333 3
1 21 Job security 50.000000 1
1 21 Materials and equiment 16.666666 2
1 21 Materials and equiment 33.333333 3
1 21 Materials and equiment 50.000000 1
1 21 Opportunity for advancement 16.666666 2
1 21 Opportunity for advancement 16.666666 3
1 21 Opportunity for advancement 66.666666 1
1 21 Growth opportunities 16.666666 3
1 21 Growth opportunities 33.333333 2
1 21 Growth opportunities 50.000000 1
1 21 Salary 16.666666 2
1 21 Salary 33.333333 3
1 21 Salary 50.000000 1
1 21 Workload 16.666666 3
1 21 Workload 33.333333 2
1 21 Workload 50.000000 1
1 22 Ability to make decision 33.333333 3
1 22 Ability to make decision 66.666666 1
1 22 Comfortable work environment 50.000000 1
1 22 Comfortable work environment 50.000000 3
1 22 Employee recognition program 16.666666 2
1 22 Employee recognition program 33.333333 3
1 22 Employee recognition program 50.000000 1
1 22 Employee training program 33.333333 3
1 22 Employee training program 66.666666 1

from this data, i just want to get row with highest percentage per question.
ie:- for question 21, suppose to get:

1 21 Access to company training 66.666666 1
1 21 Benefits 66.666666 1
1 21 Career advancement 50.000000 1
1 21 Pay and performance 50.000000 1
1 21 Pay and performance 50.000000 1
1 21 Flexibility of work hours 66.666666 1
1 21 Job security 50.000000 1
1 21 Materials and equiment 50.000000 1
1 21 Opportunity for advancement 66.666666 1
1 21 Growth opportunities 50.000000 1
1 21 Salary 50.000000 1
1 21 Workload 50.000000 1

for question 22:
1 22 Ability to make decision 66.666666 1
1 22 Comfortable work environment 50.000000 1
1 22 Comfortable work environment 50.000000 3
1 22 Employee recognition program 50.000000 1
1 22 Employee training program 66.666666 1

thanks..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-18 : 02:16:09

Add your column Question to the PARTITION BY part in the windowed function.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources