Waow, you made me come accross a bug in sql server 2k5.

A "select * from..." which works just fine, unitl I turn it into a "select count(*) from..." where the "Divide by zero error encountered." occurs for no good reason.

4660046610375530309 4

I win :p

Two things... first, what did you do with the link for the submission guidlines and second, do you guys still thing that "set based" means "all in one query"?

--Jeff Moden

Sorry Jack, those columns are in the wrong order. You lose. But yes, there's a row missing.

I guess the output result in example is missing this value

4    4660046610375530309

Let me know if i am wrong, if so, where am wrong?

I guess the output result in example is missing this value

4    4660046610375530309

Let me know if i am wrong, if so, where am wrong?

Can we assume the first 92 Fibonacci numbers are already stored in a given table, which we can use in our query?

Thanks Brad,

I got the point.

I had revised my query and re-uploaded the updated version of query.

Thanks once again.

--Pragnesh

@Jeff:

Yes, the solution has to be set-based... no temp tables, no variables, no UDF's... nothing except a single SQL statement that starts with the keyword SELECT or WITH.

--Brad

------------------------------------------------------

Why do people think that "Set Based" means "Single Query"?  That is SO wrong.  Avoiding TempTables, Table Variables, and Inline TVF's as part of the delusion that they aren't "Set Based" is also very wrong.  The requirement of doing everything in a single query has nothing to do with Set Based technology and it's sending the wrong message to new SQL Programmers.

--Jeff Moden

Not that it will make a difference in this competition, but the 93rd Fibonacci number fits in a BIGINT.  The reason why most folks don't know that is because they select the wrong variable to display when they calculate the numbers.

--Jeff Moden

I just have a test and it shows using the "sign" has no advantage over "case",hehe.

The Expected Output has the FiboNumber ordered by alfanumeric order, but it should be by numeric order.

1 55

1 144

1 233

1 377

1 4181

2 17711

2 121393

2 5702887

2 24157817

2 39088169

3 102334155

3 267914296

3 2971215073

3 12586269025

3 86267571272

4 139583862445

4 365435296162

4 591286729879

4 4052739537881

4 17167680177565

5 225851433717

5 37889062373143906

5 61305790721611591

5 259695496911122585

5 1100087778366101931

6 14472334024676221

6 23416728348467685

6 420196140727489673

7 679891637638612258

Please ignore my comment of my previous post.

Please correct me if I'm wrong but the correct output, acording to the rules should be the one I posted previously. For example the five first FibNumbers with single repeating digit are:

1 55

1 144

1 233

1 377

1 4181

Regards,

Pedro Cunha

Ok...Adjacent...

Sorry for the confusion.

i was not able to upload my solution

i got this message

"Sorry, there was a problem with your last request!

Either the site is offline or an unhandled error occurred. We apologize and have logged the error. Please try your request again or if you know who your site administrator is let them know too."

how should I submit my solution?

Thanks Ashish ^_^

------------------------------------------------------

Quote:

Why do people think that "Set Based" means "Single Query"?  That is SO wrong.  Avoiding TempTables, Table Variables, and Inline TVF's as part of the delusion that they aren't "Set Based" is also very wrong.  The requirement of doing everything in a single query has nothing to do with Set Based technology and it's sending the wrong message to new SQL Programmers.

--Jeff Moden

------------------------------------------------------

It seems like there are some more folks out there having a hard time to understand your definition of "set based"... I made a similar comment in the challenge 19 forum.

If your requirement is, "the solution must start with either SELECT or WITH", why doesn't the winning solution for challenge 14 meet that requirement (it starts with DBCC followed by a SET command...)??

The SET NOCOUNT ON can make a big difference in terms of duration when doing the load test... Please clarify.

Btw: What are the rules in terms of including the sample data you provided (e.g. temp table for challenge #19)? Are we supposed to include it (meaning to fail your major requirement) or are we supposed to leave it out (meaning to fail because the code won't run).

It would help ALOT if you couldclarify your requirements...

Regards,

Lutz

Jacob wrote...

______________________________________________

Please note that our goal is NOT to define what is SET BASED and what is NOT.

_______________________________________________

But, you HAVE defined it by saying that it must be "Set Based" and that it must be done in a "single query" and, yes, you have started the "argument".  I say "argument" because I certainly take exception to it mostly because I've seen the damage that can be done by people who think that "Set Based" means "single query" as you and your supporters have clearly stated as a requirement for these challenges.  It simply sends the wrong message to anyone who may be trying to learn about SQL Server.

Respectfully.

--Jeff Moden

Brad said...

_____________________________________________________

Perhaps "set-based" is not the correct term... that terminology may be from older times in trying to disqualify CURSOR-based solutions.  We can change the guidelines to state the requirements more explicitly.

Regarding your other comment:  Assuming the 1st Fibo number is 1 and the 2nd Fibo number is also 1 and the 3rd Fibo number is 2 and the 4th is 3 and so on and so on (as stated in the introduction of this challenge), the 93rd Fibo number is just over 12 Quintillion (12,200,160,415,121,876,738).  

_________________________________________________

Thanks for the feedback, Brad.  My real point is that it doesn't matter what you call it, requiring people to meet these challenges in a single query is the wrong way to write effective T-SQL and it sends the wrong message to relative newbies as to how to write code.  I forget which challenge it was but my multi-part set based code beat the winner's code by a factor of 26 times yet my code was rejected because it wasn't a single query.

And real FIB numbers don't start at 1... by definition, they must start at "0". ;-)

--Jeff Moden

@Jacob,

My suggestion, then, would be for you to simply remove the term "Set Based" from your good site because it is not "Set Based" code that the challenges seek.  Consider this... you good folks do allow recursive CTE's and they are patently not set based any more than a nice "Triangular Join" is.

Anyway, thank you for the feedback old friend.

Sincerely,

--Jeff Moden

Jeff, you have a fan.

Most notable quote: "And screw the boss... it's not his/her hiney"  

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:

Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

@ Jacob:

"Does it look better now?

beyondrelational.com/.../tsql-challenges-terms-and-conditions-submission-guidelines-evaluation-process-and-faq.aspx"

----------------------------

Sure it does!!! Thank you very much!

May I suggest guideline item #5 as: Your file should NOT include the sample data provided with the challenge. Provide your single query only (including comments if desired).

(This is actually a proposal based on your statement "So by NOT including the sample data, you make our lives easier" ...)

Hello,

What is the submission cutoff date/time for Challenge #20?

Thanks,

Stephen

I didn't put a order by fibonumber clause in my query (I forgot about it).

But the result is the same since SQL Server use sequential order of item creation.

But since, we aren't supposed to rely on that, does that makes my solution not acceptable?

Eric Freeman said:

Jeff, you have a fan.

______________________________________________________

Heh... Welcome to the "dark side", Eric. ;-)

Jeff said...

_____________________________________________________

...Consider this... you good folks do allow recursive CTE's and they are patently not set based any more than a nice "Triangular Join" is...

_____________________________________________________

We usually agree on these things, but here's where I would have to disagree with you, Jeff.  So called Traingualr Joins are perfectly set-based according to any definition of set-based that I know of (and certainly according to the ones that I subscribe to).  Yes, most SQL compilers implement them in a horrbly slow manner, but that is irrespective of whether they are set-based or not.  Set-based does not mean "the opposite of Slow", it may mean "the opposite of incremental", but that still leaves plenty of room for nice set-based code that takes for ever for the average SQL compiler to rub.

On th other hand we are in complete agreement about recursion not being set-based, but somehow Marvin Minsky and Lisp convinced everyone in CS that recursion is both Declarative and set-based, though every mathematician has known otherwise since the 1900's.

How many times can a single instance of a single thing exist in a single set, Barry?  Triangular joins read each row more than once and are not set based.

Jeff said...

_____________________________________________________

...How many times can a single instance of a single thing exist in a single set, Barry?  Triangular joins read each row more than once and are not set based...

_____________________________________________________

Jeff, if that were true, then all self-joins would be non-set-based (and cross-joins ould be right out).  In fact, anytime that same value appeared twice in the same non-key column, it would make it a non-set.  Heck, just the number "1" showing more than once anywhere in a table, in any columns would make it non-set based.  And by extension, none of these things would be allowed or even possible in a "true" relational database, and I shouldn't be able to formulate them in Predicate Logic (analogous to Set Theory formula).  

But everyone of these things is, in fact still a set, they are all alowed in "true" relational databases (in fact Codd himself was the first to demonstrate self-joins in an article he wrote), and it is quite easy for me to formulate a classic triangular join directly in predicate logic.  In fact, even the  hideous (in SQL) cross-join is one of the most basic operations in all of Set Theory, and funsamnet to all arithmetic (because it necessary to use a cross-join to define Multiplication).

And the reason why is that the "Sets" of SQL are tables and rowsets, and what constitutes their member (or "elements" in Set Theory) is *not* individual data values or even columns, but entire Rows (which is to say that Tables are not "simple" Sets, but rather, complex, nested sets).  This is why Triangular Joins and Cross Joins are perfectly valid sets: they take two sets of smaller rows (possibly the same sets) and then make a new Set by combining them in a "bijection", that is, making a Set of much bigger rows by concatenating two rows (one from each source set) into a much bigger row.  So as long as these bigger, concatened rows are not duplicated in the output, it's still a set.

Thus, if you start with a table that consists of the Set of 2 rows {[a],[b]} and then Cross Join it with itself you get {[a,a], [a,b], [b,a], [b,b]}, which is still a Set.  Some might argue that it is NOT a Set because the element (a) appears 3 times in the output, however, [a] itself *never* actually appears as a member of the output set, it only appears as a sub-element of the *rows* of the output set (rows themselves are Lists, another kind of complex nested set).

So, summing up (*whew!*), even enormous things like Cross-Joins and Triangular-Joins (so-called)  are still Set-based.  Note that I say "so-called" Triangular-Joins because the SQL queries that usually have this label attached to them, have nothing in the logic of the SQL query itself that forces them to be Triangular, its just that the SQL compiler writers (Microsoft, in our case) choose not to optimize those queries very well and instead implement them internally with Triangular loops.

_____________________________________________________

( *gack!*  Sorry for going for so long everyone it's just that Set Theory is near and dear to my heart (it was my major in college) and once I got going, I just couldn't seem to stop ... :-) )

Barry said...

_____________________________________________________

...the reason why is that the "Sets" of SQL are tables and rowsets, and what constitutes their members (or "elements" in Set Theory) is *not* individual data values or even columns, but entire Rows ...

_____________________________________________________

I forgot to mention Jeff, but this is also why your saying:

 "Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column"

--Jeff Moden

... rings so true, focusing on Rows means focusing on the individual members of the Sets, and thats not very

set-oriented.  Whereas focusing on the columns means thinking about the common (or distinctive) attributes of those members, which is frequently the basis for forming new sets in Set Theory.

I agree with everything you said about sets (heh... you should write a book on sets) but not when it comes to the way triangular joins are typically used and what they must do behind the scenes.

I do, however, agree that a triangular join without "SUM" (or whatever) is set based the same way that a cross join can be set based (ie: used properly).

Or, if you'd rather... both are RBAR on steroids and it's the simple speed of the Pseudo Cursors behind the scenes makes them acceptable when used properly.

Perhaps I should have clarified my statement and included a usage qualifier.

Thanks for the feedback, Barry.  

On the subject of "single query" vs. "set based".  I "talked" with Jacob and I understand "the problem", now.  The evaluation software they use can't currently use anything other than a "single query" with all the other restrictions that go with that (heh... I wish they'd just come out and said that earlier).  It's the only way they can evaluate entries without it taking a month of Sundays.

Not that I'll actually be able to find a solution for that problem, but I'm going to give it the ol' college try.

Jeff said ...

 I agree with everything you said about sets (heh... you should write a book on sets) but not when it comes to the way triangular joins are typically used and what they must do behind the scenes.

 I do, however, agree that a triangular join without "SUM" (or whatever) is set based the same way that a cross join can be set based (ie: used properly). ...

_____________________________________________________

I've actually been thinking about  this quite a lot in the last 6 months, (especially since I became bed-ridden 10 weeks ago) and I've come to a number of interesting conclusions that you might want to consider also...

But maybe we should give everyone else a break and take this off-line!  :-)  ...

Sure... Peter sent us both an email on the subject, as well.  

Is there a way to modify my solution or should i just re-upload my file? My solution has the wrong column names specified in the final select.

How is this challenge going to be load tested?

I like the challenges and requirements. Even though I would not necessarily code TSQL this way for my job, I find this kind of thing overall expands my thinking.

One consideration for always on is that once winners are announced, people are able to see the already submitted solutions. This leave open the possibility of a person just taking a couple of good ideas from these existing solutions and combining/tweaking them for a better solution - with little or no creativity on his part.

It would not seem fair to update winners to bubble such a solution to the top.

Jacob,

     I submitted 4 solutions. At the time of evaluation, team will consider all the 4 solutions or just the latest solution that we submitted??

Two quick questions,

What is the submission cutoff date/time for Challenge #20?

I understand the a number like 1234412344123 would count as 1 repeating sequence, but what about 12344123444123?  Does 44 and 444 count as 1 or two?

Thanks,

Steve

Steve,

44 and 444 will count as 1.

No. Of Distinct Digits repeated atleast twice in the number...

suppose for eg, 441444 ---- its only 4 is repeated. so num of repeats is 1.

if the number is 44144455 -- now here 4,5 are repeated... so no of repeats are 2..

Thanks Rami!

Now do we have a deadline?  I need at least a week to read and understand the problem and a month or two to solve....

No, There is no dead line...

You can take 2 months also... :). I believe evaluation of the challenge 20, will take until that time...

Even after sibmission of results also, u can come up with an fresh idea, if it is performing better than others, stars(points) will come to that solution..

I believe the second last line in your sample results is wrong.  I get the following:

NumRepeats  FiboNumber

----------- --------------------

1           55

1           144

1           233

1           377

1           317811

2           17711

2           102334155

2           433494437

2           32951280099

2           53316291173

3           27777890035288

3           117669030460994

3           5527939700884757

3           14472334024676222

3           259695496911122660

4           1100087778366102300

Absolutely right.  Thank you.

Actually it was caused by the STR function.

select str(14472334024676221, 20)

--------------------

  14472334024676220

(1 row(s) affected)

Probably caused by the implicit conversion to float.

Live and learn.

Do you have to compute the Fibonacci numbers or can you just look them up somewhere and put a CTE with UNION all literals (everybody seems to be trying to calculate them though)