SQL Server's MEDIAN function. 1

[grnzbra]

I went into the HELP files and typed in MEDIAN and got the following:
Median
Returns the median value of a numeric expression evaluated over a set.

Syntax
Median(«Set»[, «Numeric Expression»])

Remarks
The Median function returns the median value of a numeric expression, specified in «Numeric Expression», evaluated over a set specified in «Set».

Example
The following example returns 2000 if respective Sales of the countries are 1000, 2000, and 3000:

Median({USA, CANADA, MEXICO}, Sales)

In my SP, I have a sorted one field temp table, @Median, with the field RcvFld. Could someone tell me how to make the MEDIAN function work with that table?

 

[vongrunt]

MEDIAN() is OLAP function, right?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Grnzbra, aside from the OLAP issue, are you aware that Median is different than Average? The example shown hides that fact. Median of {1, 2, 10} is 2.
If you do want median and you have a sorted list, then just do something like:

[Blue]DECLARE[/Blue] @Med [Blue]AS[/Blue] [Blue]int[/Blue]  
[Blue]SELECT[/Blue] [Blue]TOP[/Blue] 50 [Blue]PERCENT[/Blue]  @Med[Gray]=[/Gray]YourColumn 
   [Blue]FROM[/Blue] YourTable 
   [Blue]ORDER[/Blue] [Blue]BY[/Blue] YourColumn

If you need percision, then you have to worry about the case where Count(*) is even and take the average of two values.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Kewl, this should work for all cases:

[Blue]DECLARE[/Blue] @Med [Blue]AS[/Blue] [Blue]int[/Blue]
[Blue]DECLARE[/Blue] @Med2 [Blue]AS[/Blue] [Blue]int[/Blue]  
[Blue]SELECT[/Blue] [Blue]TOP[/Blue] 50 [Blue]PERCENT[/Blue]  @Med[Gray]=[/Gray]YourColumn 
   [Blue]FROM[/Blue] YourTable 
   [Blue]ORDER[/Blue] [Blue]BY[/Blue] YourColumn
[Blue]SELECT[/Blue] [Blue]TOP[/Blue] 50 [Blue]PERCENT[/Blue]  @Med2[Gray]=[/Gray]YourColumn 
   [Blue]FROM[/Blue] YourTable 
   [Blue]ORDER[/Blue] [Blue]BY[/Blue] YourColumn [Blue]DESC[/Blue]
[Blue]SET[/Blue] @Med[Gray]=[/Gray][Gray]([/Gray]@Med[Gray]+[/Gray]@Med2[Gray])[/Gray]/2
[Blue]SELECT[/Blue] @Med

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Hehe... I like it. Especially compared to faq183-4751.

Btw. take care about integer math.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

You've got to love it...an FAQ that uses a cursor! Come'n vongrunt, you write that performance FAQ and I'll write a cursor FAQ.
So what gives with your sig line? Am I missing something...is that more than gibberish?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

How long before someone points out that I've authored two FAQs using dynamic SQL!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

How about FAQ or two for mere mortals first? Just to get in shape... Any proposal?

Btw. here is the best cursor FAQ I saw so far.

> So what gives with your sig line?
42

Btw2. at the moment dynamic SQL is kind of necessary for pivoting data so...

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[ookete]

I know this is off the subject, vongrunt, but could you point me to a good reference about pivoting data (FAQ, post, or whatever)?

 

[vongrunt]

There are two FAQs donutman is referring to. Just search FAQ section for this forum (kwd: Crosstab).

If you think about thread183-1005540, things may be different. Personally I prefer to pivot data client-side... and never give users an illusion they work on a flat table. This is definitely material for another topic.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Wow...you're going way over my head. Mere mortal FAQ? 42? The only thing I understood was Nigel's outstanding FAQ!
You can't be referring to decimals and rounding issues with the mere mortal reference. It wouldn't be a trivial FAQ but it's a pretty minor subject.
If that sig line isn't gibberish, then you should give a hint and 42 ISN'T enough for mere mortals!
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Ya know, title "Performance difference between IN/EXISTS and derived tables in correlated subqueries" is kind of scary. At least it would scare me at 03:00 AM. So before I learn how to write shorter titles, I was thinking about writing less demanding FAQ or two. How about "Working with dates/times" or something?

Sig line is of course gibberish... two unknown variables, one complex number...

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Of course, that's what I originally thought, but I became ambivalent because you've kept it so long and coming from you I knew it would require thinking outside the box of a traditional math problem.
That reference to mere mortals crossed my mind too...you geniuses are just to hung-up on avoiding a mistake as if it would diminish your self-worth. Make them more often and it won't bother you so much...as a mere mortal that's what I do.
3:00am, where are you from anyway?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

From the other side of Great Swamp... now it's 19:30 here.

Seriously, can you propose some ideas for FAQ?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Well, the cursor one is clear to me. Find every post where poster thought that a cursor was necessary and show a link to the solution. Explain where a cursor is a must. Challenge readers to submit a problem that can't be solved faster by a set-based solution.
The performance FAQ that I'm imagining is one that admittedly would always be a work in progress. It shouldn't be thought of as the final answer, because I really doubt that there is one regarding In/Exists/Derived Tables/Correlated Subquery. Everyone's server/situation is different. But it could provide some insights that are useful starting points. Because it's such a large topic, I'm thinking it should be organized into Parts I, II, III etc.
As of right now, I'm completely confused about the 3+ ways to go. I used to think that the IN and correlated subquery approaches were bad. Now, I don't which way to approach a problem...and who but ESquared wants to think of all solutions and compare them.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Is it so bad to want to know everything, or to make a good stab at trying?

 

[donutman]

The comment about you're instance upon finding all solutions was more of a compliment then a critisism. It's only when it goes beyond practical value, that it makes me chuckle...and probably because I'm as guilty as you in my own way.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

You're doing it on purpose now! Gah!

 

[donutman]

Oh, you caught the then.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]