| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
 Posted - 05/16/2001 : 17:17:59
|
Please DO NOT POST SOLUTIONS HERE! Email them to graz (graz@sqlteam.com).
===============================================
Creating tomorrow's legacy systems today.
One crisis at a time. |
 |
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 05/16/2001 : 17:36:23
|
Cool man! This will give me a new way to avoid actual work! 
|
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 05/17/2001 : 00:32:31
|
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. |
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 05/19/2001 : 10:31:57
|
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. |
|
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 05/22/2001 : 18:34:38
|
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.
|
|
|
|
Tim
Starting Member
Australia
392 Posts |
Posted - 05/23/2001 : 01:00:07
|
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 ;-) |
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 05/23/2001 : 01:17:42
|
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. |
|
|
|
Tim
Starting Member
Australia
392 Posts |
Posted - 05/23/2001 : 01:19:24
|
hehehe
well at least i didnt say tomorrow's challenge :p
|
|
|
|
jcelko
Esteemed SQL Purist
USA
547 Posts |
Posted - 06/19/2001 : 20:36:45
|
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
|
|
|
|
mfemenel
Professor Frink
USA
1414 Posts |
Posted - 06/19/2001 : 20:59:20
|
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 |
|
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 06/19/2001 : 22:00:21
|
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!
|
|
|
|
mfemenel
Professor Frink
USA
1414 Posts |
Posted - 06/19/2001 : 22:48:42
|
So you're still at work. Excellent. Hell evening.
Mike F
|
|
|
|
shurley
Yak Posting Veteran
USA
90 Posts |
Posted - 06/20/2001 : 02:08:37
|
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 |
|
|
|
jcelko
Esteemed SQL Purist
USA
547 Posts |
Posted - 06/20/2001 : 11:40:49
|
I juzst noticed that I left the END keyword out of my CASE expressions!! Arrrgh!
--CELKO--
Joe Celko, SQL Guru
|
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 06/20/2001 : 11:57:12
|
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
|
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 06/20/2001 : 21:34:30
|
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. |
|
|
| |
Topic |
|
Article: Reader Challenge
