FizzBuzz

Question

FizzBuzz

Viewing 15 posts - 76 through 90 (of 363 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

James Goodwin (2/22/2010)
Here's the thing that gets me:
Why is everyone using CASE WHEN (number % 5)=0 And (number % 3)=0, instead of CASE WHEN (number %15) = 0?
I want to see that you've thought about the problem, not just mimicked the specification.
--
JimFive

Self-documentation. Makes it clear what the specification was vs what was written.

Or, in the case of my "ultimate version", for the sake of inefficiency.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

Agree with Gus on the self-documentation, makes it clearer WHY you're doing it. Comments could do that too, suppose.

Sean, on yours, casting is unneccessary until the ELSE, and makes it more confusing IMHO. Nice use of ROW_NUMBER() on this one.

select top 100

case

when ROW_NUMBER() over (order by sc1.name)%3 = 0

and ROW_NUMBER() over (order by sc1.name)%5 = 0 then 'FizzBuzz'

when ROW_NUMBER() over (order by sc1.name)%3 = 0 then 'Fizz'

when ROW_NUMBER() over (order by sc1.name)%5 = 0 then 'Buzz'

else cast(ROW_NUMBER() over (order by sc1.name) as varchar(8))

end

from sys.columns sc1,

sys.columns sc2

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

jcrawf02 (2/22/2010)
Agree with Gus on the self-documentation, makes it clearer WHY you're doing it. Comments could do that too, suppose.
Sean, on yours, casting is unneccessary until the ELSE, and makes it more confusing IMHO. Nice use of ROW_NUMBER() on this one.
select top 100
case
when ROW_NUMBER() over (order by sc1.name)%3 = 0
and ROW_NUMBER() over (order by sc1.name)%5 = 0 then 'FizzBuzz'
when ROW_NUMBER() over (order by sc1.name)%3 = 0 then 'Fizz'
when ROW_NUMBER() over (order by sc1.name)%5 = 0 then 'Buzz'
else cast(ROW_NUMBER() over (order by sc1.name) as varchar(8))
end
from sys.columns sc1,
sys.columns sc2

This is a nice solution and has the previously mentioned issue of scale. It won't support 1 million records. That is easily adjusted though - was shown in a previous post.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 4

CirquedeSQLeil (2/22/2010)
jcrawf02 (2/22/2010)
Agree with Gus on the self-documentation, makes it clearer WHY you're doing it. Comments could do that too, suppose.
Sean, on yours, casting is unneccessary until the ELSE, and makes it more confusing IMHO. Nice use of ROW_NUMBER() on this one.
select top 100
case
when ROW_NUMBER() over (order by sc1.name)%3 = 0
and ROW_NUMBER() over (order by sc1.name)%5 = 0 then 'FizzBuzz'
when ROW_NUMBER() over (order by sc1.name)%3 = 0 then 'Fizz'
when ROW_NUMBER() over (order by sc1.name)%5 = 0 then 'Buzz'
else cast(ROW_NUMBER() over (order by sc1.name) as varchar(8))
end
from sys.columns sc1,
sys.columns sc2
This is a nice solution and has the previously mentioned issue of scale. It won't support 1 million records. That is easily adjusted though - was shown in a previous post.

Heh...yeah... I wonder how many people will realize that.

--Jeff Moden

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 5

Sean-752587 (2/22/2010)
It did take me a fair bit longer than 10 minutes, but I think the result is a good one. At first I was working with number tables (as suggested by Jeff Moden)

Nope... I actually didn't suggest using a numbers table. I suggested using the methods to build one because that's what's needed to make this set based.

--Jeff Moden

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 6

jcrawf02 (2/22/2010)
Agree with Gus on the self-documentation, makes it clearer WHY you're doing it. Comments could do that too, suppose.
Sean, on yours, casting is unneccessary until the ELSE, and makes it more confusing IMHO. Nice use of ROW_NUMBER() on this one.
select top 100
case
when ROW_NUMBER() over (order by sc1.name)%3 = 0
and ROW_NUMBER() over (order by sc1.name)%5 = 0 then 'FizzBuzz'
when ROW_NUMBER() over (order by sc1.name)%3 = 0 then 'Fizz'
when ROW_NUMBER() over (order by sc1.name)%5 = 0 then 'Buzz'
else cast(ROW_NUMBER() over (order by sc1.name) as varchar(8))
end
from sys.columns sc1,
sys.columns sc2

"It depends". The requirements are sometimes worded as "the multiple of 3 and 5" in which case, using a separate 3 and 5 doesn't actually meet the spec. 🙂

--Jeff Moden

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

GSquared (2/22/2010)
James Goodwin (2/22/2010)
Here's the thing that gets me:
Why is everyone using CASE WHEN (number % 5)=0 And (number % 3)=0, instead of CASE WHEN (number %15) = 0?
I want to see that you've thought about the problem, not just mimicked the specification.
--
JimFive
Self-documentation. Makes it clear what the specification was vs what was written.
Or, in the case of my "ultimate version", for the sake of inefficiency.

As opposed to

select top 100

case

when ROW_NUMBER() over (order by sc1.name)%15 = 0 --Multiples of 3 AND 5

then 'FizzBuzz'

when ROW_NUMBER() over (order by sc1.name)%3 = 0 then 'Fizz' --multiples of 3 only

when ROW_NUMBER() over (order by sc1.name)%5 = 0 then 'Buzz' --multiples of 5 only

else cast(ROW_NUMBER() over (order by sc1.name) as varchar(8))

end

from sys.columns sc1,

sys.columns sc2

I don't know - 15 seems cleaner to me that repeating 3's and 5's many times over.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 8

Sean-752587 (2/22/2010)
So I think the lesson learned for me, is that I need to put a bit more effort into the way I approach problem solving and I need to get my head around the whole concept of set based thinking.

Based on that very fine observation, me taking hits whilst standing on my soapbox is well worth it for me to see folks make that particular revelation. Well done, Sean.

Just be carefull of what you might be using for a row source. sys.Columns on the local database may not have enough rows if the database is "young". Master.sys.All_Columns has not been deprecated and will have over 4,000 rows even on a brand new server (never mind database).

--Jeff Moden

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 9

It took me a little over 4 minutes. I've had a similar question to this in an interview. I don't remember the exact question. I wasn't given a computer just a whiteboard marker and eraser. My first answer before writting on the board was that something like this is probably better off written in the client app and not t-sql. I proceeded to ask a few additional details about what the requester was looking for. Then I wrote 2 different versions (ways) of completing it and the pros and cons of each. I also had the opportunity to teach who was to be my future boss why cursor based solutions are usually not the answer.

I got the job. What the interviewer was looking for was the thought process going into it, not the exact solution. He was also looking for someone who knew how to gather requirements and someone who he felt would meld into his team. It ended up that the scenario he put on the board was one that he had been stumped with and ended up using my pseudo solution to solve his problem.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Sean Cowburn SSC Veteran Points: 211 More actions · Answer 10

Hi jcrawf02

How would you change it to avoid the cast?

Hmm... The reply didn't work the way I thought it would.

My question was in regard to your comment on my solution

select top 100

case

when cast(ROW_NUMBER() over (order by sc1.name) as int)%3 = 0

and cast(ROW_NUMBER() over (order by sc1.name) as int)%5 = 0 then 'FizzBuzz'

when cast(ROW_NUMBER() over (order by sc1.name) as int)%3 = 0 then 'Fizz'

when cast(ROW_NUMBER() over (order by sc1.name) as int)%5 = 0 then 'Buzz'

else cast(ROW_NUMBER() over (order by sc1.name) as varchar(8))

end

from sys.columns sc1,

sys.columns sc2

Sean Cowburn SSC Veteran Points: 211 More actions · Answer 11

Jason and Jeff

As far as scaleability is concerned, I did try it with 1 Mil. rows which didn't work in my master database (SQL 2008 dev edition). It topped out at 434281 rows.

It did however work in our dev database on a test server. It ran in a little over 18 seconds.

Even if there aren't enough rows in the source tables, what do you think of a 2nd cross join?

...

from sys.columns sc1,

sys.columns sc2,

sys.columns sc3

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Sean-752587 (2/22/2010)
Jason
As far as scaleability is concerned, I did try it with 1 Mil. rows which didn't work in my master database (SQL 2008 dev edition). It topped out at 434281 rows.
It did however work in our dev database on a test server. It ran in a little over 18 seconds.

The cast was corrected in Crawford's post. It is only necessary in the ELSE statement.

The scalability - that is the nature of sys.columns. You may be able to get the necessary 1 million rows, you may not be able to - as you experienced in your environments. Jeff makes a suggestion to avoid this by using an alternative. Tim Thomson also clarified this a little in his posts. Tim used a different process altogether. He also mentioned adding another sys.columns join in an earlier post.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 13

Sean-752587 (2/22/2010)
Hi jcrawf02
How would you change it to avoid the cast?

like this... you don't need to cast ROW_NUMBER to an int... it already is

select top 100

case

when ROW_NUMBER() over (order by sc1.name) %3 = 0

and ROW_NUMBER() over (order by sc1.name) %5 = 0 then 'FizzBuzz'

when ROW_NUMBER() over (order by sc1.name) %3 = 0 then 'Fizz'

when ROW_NUMBER() over (order by sc1.name) %5 = 0 then 'Buzz'

else cast(ROW_NUMBER() over (order by sc1.name) as varchar(8))

end

from sys.columns sc1,

sys.columns sc2

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Cowburn SSC Veteran Points: 211 More actions · Answer 14

WayneS (2/22/2010)
Sean-752587 (2/22/2010)
Hi jcrawf02
How would you change it to avoid the cast?
like this... you don't need to cast ROW_NUMBER to an int... it already is
select top 100
case
when ROW_NUMBER() over (order by sc1.name) %3 = 0
and ROW_NUMBER() over (order by sc1.name) %5 = 0 then 'FizzBuzz'
when ROW_NUMBER() over (order by sc1.name) %3 = 0 then 'Fizz'
when ROW_NUMBER() over (order by sc1.name) %5 = 0 then 'Buzz'
else cast(ROW_NUMBER() over (order by sc1.name) as varchar(8))
end
from sys.columns sc1,
sys.columns sc2

He who can read...

Thanks. Looks like a typical copy and paste error.

Shouldn't have happened though.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 15

Sean-752587 (2/22/2010)[hr
He who can read...

lol, I had the same feeling when others pointed out it was in previous posts. The one time I skip to the end...:-P

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."