Need to create index for the resultened query and retrieve the data from the second column

Question

Need to create index for the resultened query and retrieve the data from the second column

poornima.s_pdi

SSCrazy

Points: 2720
More actions
January 12, 2017 at 7:23 pm

#339271

Hi,
I am using the following query,
select t.range as [score range], count(*) as [number of occurences]
from (
select case
    when answer_count between 0 and 5 then ' 0- 9'
    when answer_count between 5 and 10 then '10-19'
    else '20-99' end as range
from points where type='product_quiz') t
group by t.range
The output is,
score range    number of occurences
10-19                  121327
0- 9                    129195
Now i need to create index for the resultened query and then i need to print only the number of occurences as,
number of occurences
129195
121327
Please help me how to achieve this using index instead of using while loop

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

poornima.s_pdi - Thursday, January 12, 2017 7:23 PM
Hi,
I am using the following query,
select t.range as [score range], count(*) as [number of occurences]
from (
select case
    when answer_count between 0 and 5 then ' 0- 9'
    when answer_count between 5 and 10 then '10-19'
    else '20-99' end as range
from points where type='product_quiz') t
group by t.range
The output is,
score range    number of occurences
10-19                  121327
0- 9                    129195
Now i need to create index for the resultened query and then i need to print only the number of occurences as,
number of occurences
129195
121327
Please help me how to achieve this using index instead of using while loop

Use ORDER BY. t.range should be ok.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 2

Hi,

I need to get the output from the resultened query.
Please try to achieve this.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

poornima.s_pdi - Friday, January 13, 2017 2:05 AM
Hi,
I need to get the output from the resultened query.
Please try to achieve this.

Did you try ORDER BY?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 4

Hi,

Using orderby also i never get the expected result.
Because from the resultened output i need to display only 129195 from number of occurences column..

Thanks,
Poornima

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

poornima.s_pdi - Friday, January 13, 2017 2:31 AM
Hi,
Using orderby also i never get the expected result.
Because from the resultened output i need to display only 129195 from number of occurences column..
Thanks,
Poornima

"Now i need to create index for the resultened query and then i need to print only the number of occurences as,
number of occurences
129195
121327
Please help me how to achieve this using index instead of using while loop"

So which do you want in your output, one row or two?
If you want only one row, what are the rules for deciding which row to return?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 6

ChrisM@Work - Friday, January 13, 2017 2:45 AM
poornima.s_pdi - Friday, January 13, 2017 2:31 AM
Hi,
Using orderby also i never get the expected result.
Because from the resultened output i need to display only 129195 from number of occurences column..
Thanks,
Poornima
"Now i need to create index for the resultened query and then i need to print only the number of occurences as,
number of occurences
129195
121327
Please help me how to achieve this using index instead of using while loop"
So which do you want in your output, one row or two?
If you want only one row, what are the rules for deciding which row to return?

only the single column value "129195" from the resulting query

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

This will work, but I suspect it's not what you're looking for. So are you going to share the business rules with us, or wait for us to get bored of guessing? Remember, we can't see what's on your screen and we're not familiar with your application.
SELECT 129195 AS [number of occurences]

John

DesNorton SSC-Insane Points: 24387 More actions · Answer 8

You can try this
select TOP 1 count(*) as [number of occurences] from ( select case when answer_count between 0 and 5 then ' 0- 9' when answer_count between 5 and 10 then '10-19' else '20-99' end as range from points where type='product_quiz') t group by t.range order by count(*) desc

How to post data/code on a forum to get the best help.

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 9

Thanks DesNorton.
But never used indexing concepts it seems.
I hope this will be useful..Thank you very much for timely help

Best Regards,
Poornima