Median Values query?? - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Median Values query??

Author

Topic

mermaidhaven
Starting Member

9 Posts

Posted - 2001-12-10 : 16:23:41

All,
I am writing a stored procedure that is going to load a temp table with some values. Some of the value I need to incur are quartile values. Meaning if there are 100 records in my recordset I need to get the .50 record , the 75th record and the 25th record .. ordered by some value. Now I know I can get the recordcount and multiply it my .75 to get the 75% record, but we are talking 20000 records here. That will take way to long. Is there a better way? I know SQL has some upgrade that does allow you to do this, but we do not have that, and it is not an option. So what is available to me?
Thanks
Samantha

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2001-12-10 : 18:08:03

SAMANTHA <<< the name of my QUANT server...
Hi sam..

Try this...

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9981[/url]

Exactly what you want.. Maybe with a little bit of modifications..

My dislexia got the better of me the last reply..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2001-12-10 : 18:17:33

Sam, sorry maybe if that's not clear enough, the bit of code that goes like:

select @pos1 = (count(*)+1)/2, @pos2 = count(*)/2 + 1 from #Values

if you want quarters then you divide by 4 or multiply by .25, .5, .75, etc.. you're a smart cookie, you'll figure it out easy enough.. By the way byrmol provided the solution.. He's still the man..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-10 : 18:31:20

I cannot take credit for the procedural based code...

I leveraged/repurposed (ie stole) that..

DavidM
It gets windy at a thousand feet...."Tutorial D"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-11 : 04:23:45

Surely the numbers need to be in the right order first for that?

Edited by - Arnold Fribble on 12/11/2001 04:41:33

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2001-12-11 : 09:20:20

Arnold, yes of course, but the first part is only to get the position then the second part of the solution which is not clarified o what the heck:

set rowcount @pos1
select @one = Value from #Values

set rowcount @pos2
select @two = Value from #Values

Will need to be ordered by, so we get the proper median. This was just an example and if you were after the median you would be order-ing by default anyway.. Hope that clears it up... And thanks to the wonderful hint, I now know how to Drink beer!! Bottoms UP!!