| 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" |
|
|
|
|
call a column, but don't want to group by