SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Reader Challenge
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/16/2001 :  17:14:33  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Welcome to our first Reader Challenge. For almost a year we've been reading your questions and trying to answer some of them.  JustinBigelow suggested we give you that same chance.  Read on for the question and a test of your SQL skills!

Article Link.

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 05/16/2001 :  17:17:59  Show Profile  Visit graz's Homepage  Reply with Quote
Please DO NOT POST SOLUTIONS HERE! Email them to graz (graz@sqlteam.com).

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 05/16/2001 :  17:36:23  Show Profile  Reply with Quote
Cool man! This will give me a new way to avoid actual work!

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 05/17/2001 :  00:32:31  Show Profile  Visit graz's Homepage  Reply with Quote
So far so good. As I write this I have four solutions submitted so far. They are all pretty different too. I thought people would come up with very similar types of solutions. This should be pretty interesting. Keep them coming!

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 05/19/2001 :  10:31:57  Show Profile  Visit graz's Homepage  Reply with Quote
Just thought I'd let you all know that I'm up to 14 solutions submitted so far.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 05/22/2001 :  18:34:38  Show Profile  Visit robvolk's Homepage  Reply with Quote
I have a possible idea for a prize to the winner: a new title! Something that distinguishes them from the other "Relational SQL Masters" and "Master Smack Fu SQL Hackers". Other than just appending "Reader Challenge God" to their title, though, I'm lost for suggestions.

Go to Top of Page

Tim
Starting Member

Australia
392 Posts

Posted - 05/23/2001 :  01:00:07  Show Profile  Reply with Quote
Just a note to say I think this was a cool idea and a lot of fun (in a geeky sort of way)

And I like Robs idea of picking your own title if you win.

Looking forward to the results, and next weeks challenge ;-)
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 05/23/2001 :  01:17:42  Show Profile  Visit graz's Homepage  Reply with Quote
Next Weeks Challenge?!?!? LOL! Holy smokes I haven't even finished this one :) Oh that's funny!

Now I have to wade through 15 or so entries. Including three separate ones from "Tim" in a 56 minute span. Just couldn't quit could ya?

Next Weeks Challenge! I'm still laughing at that! ROTFL!

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Tim
Starting Member

Australia
392 Posts

Posted - 05/23/2001 :  01:19:24  Show Profile  Reply with Quote
hehehe

well at least i didnt say tomorrow's challenge :p

Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 06/19/2001 :  20:36:45  Show Profile  Visit jcelko's Homepage  Reply with Quote
Okay, I know I am late, but it was a good problem and I used in on my Geek Cruise (www.geekcruise.com) for a contest. I used to be a statistician for living mumble, mumble years ago and I would want to see the outliers.

First attempt:

SELECT D1.i,
(CASE WHEN D1.i < (SELECT (AVG(i) FROM Datapoints)
THEN 'low '
ELSE 'high') AS outlier
FROM Datapoints AS D1
WHERE D1.i NOT BETWEEN
(SELECT AVG(D2.i) - 3.0 * STDEV(D2.i)
FROM Datapoints AS D2)
AND
(SELECT AVG(D3.i) + 3.0 * STDEV(D3.i)
FROM Datapoints AS D3);


Second attempt:

SELECT D1.i,
(CASE WHEN D1.i < (SELECT (AVG(i) FROM Datapoints)
THEN 'low '
ELSE 'high') AS outlier
FROM Datapoints AS D1
WHERE D1.i NOT BETWEEN
(SELECT (AVG(i) FROM Datapoints)
- 3.0 * (SELECT STDEV(i) FROM Datapoints)
AND
(SELECT (AVG(i) FROM Datapoints)
+ 3.0 * (SELECT STDEV(i) FROM Datapoints);

The idea is that the computations (SELECT (AVG(i) FROM Datapoints) and (SELECT STDEV(i) FROM Datapoints) will be calculated once by the optimizer or (in the case of DB2 and other SQL products), can be found in the statistics for the table with a single probe.



--CELKO--
Joe Celko, SQL Guru
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 06/19/2001 :  20:59:20  Show Profile  Visit mfemenel's Homepage  Reply with Quote
A statistician. Ok, that's it! Is anyone else scared of Celko? He makes Volk look normal and well adjusted. I never thought those words would be typed by these fingers.....

Mike F


Edited by - mfemenel on 06/19/2001 21:05:17
Go to Top of Page

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 06/19/2001 :  22:00:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
Excuse me, NO ONE makes me look normal and well adjusted...

Well, maybe Charles Manson....and Marilyn Manson, but that's it! Besides, they don't know SQL!

Welcome back Joe!

Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 06/19/2001 :  22:48:42  Show Profile  Visit mfemenel's Homepage  Reply with Quote
So you're still at work. Excellent. Hell evening.

Mike F
Go to Top of Page

shurley
Yak Posting Veteran

USA
90 Posts

Posted - 06/20/2001 :  02:08:37  Show Profile  Reply with Quote
and the day slips on - work turns into SQLTeam time - projects fall by the wayside and before you know it ... its morning.

Femenella, you done with that SP yet? It's already 2:00am

Regards,

Shawn

Edited by - SHURLEY on 06/20/2001 02:50:02
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 06/20/2001 :  11:40:49  Show Profile  Visit jcelko's Homepage  Reply with Quote
I juzst noticed that I left the END keyword out of my CASE expressions!! Arrrgh!

--CELKO--
Joe Celko, SQL Guru
Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 06/20/2001 :  11:57:12  Show Profile  Reply with Quote
quote:

I juzst noticed that I left the END keyword out of my CASE expressions!! Arrrgh!

--CELKO--
Joe Celko, SQL Guru



Oh, how the mighty have fallen. j/k

Justin

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/20/2001 :  21:34:30  Show Profile  Visit nr's Homepage  Reply with Quote
Posting without checking teh cdoe?

Still think views are a good idea on sql server rather than stored procs Joe?

==========================================
Cursors are useful if you don't know sql.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000