February 22, 2010 at 9:34 am
J.Faehrmann (2/22/2010)
Gsquared your "ultimate version" is much fun to read!Now you only need to tell Jeff that you always code like that 😛
Won't work. Jeff knows my coding too well.
He's well aware that I'm more likely to really use something like this:
DECLARE @Msg VARCHAR(1000) ;
SELECT @Msg = COALESCE(@Msg + '
' + CASE WHEN Number % 3 = 0
AND Number % 5 = 0 THEN 'BizzBuzz'
WHEN Number % 3 = 0 THEN 'Bizz'
WHEN Number % 5 = 0 THEN 'Buzz'
ELSE CAST(Number AS VARCHAR(5))
END, CASE WHEN Number % 3 = 0
AND Number % 5 = 0 THEN 'BizzBuzz'
WHEN Number % 3 = 0 THEN 'Bizz'
WHEN Number % 5 = 0 THEN 'Buzz'
ELSE CAST(Number AS VARCHAR(5))
END)
FROM Common.dbo.Numbers
WHERE Number BETWEEN 1 AND 100 ;
PRINT @Msg ;
If we disregard the requirement that it's to print the output, and allow for either a dataset, or changing the output method to Text instead of a data grid, it gets even simpler:
SELECT CASE WHEN Number % 3 = 0
AND Number % 5 = 0 THEN 'BizzBuzz'
WHEN Number % 3 = 0 THEN 'Bizz'
WHEN Number % 5 = 0 THEN 'Buzz'
ELSE CAST(Number AS VARCHAR(5))
END AS BizzBuzzTest
FROM Common.dbo.Numbers
WHERE Number BETWEEN 1 AND 100
ORDER BY Number ;
This, of course, operates on the assumption that I've got a database named "Common" with a Numbers table in it. Since that's one of the first tools I set up for myself when working on a server, that assumption is valid enough. Without that, I'll have to set up either a temp table, or whatever else would be allowed on the server I'm working on.
- 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
February 22, 2010 at 9:35 am
Steve Cullen (2/22/2010)
If you're going to setup your table with a while loop, just for a set-based solution, wouldn't the direct while loop be quicker without the table? Such as :
declare @i intselect @i = 1
while @i <= 100
begin
if (@i % 3 = 0) and (@i % 5 = 0 )
print 'fizbuz'
else if (@i % 3 = 0)
print 'fiz'
else if (@i % 5 = 0)
print 'buz'
else
print @i
select @i += 1
end
Wouldn't this be faster?
Yes.
- 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
February 22, 2010 at 9:36 am
Jeff Moden (2/22/2010)
Loner (2/22/2010)
JeffWhy don't you post your 'perfect' answer?
I can tell you why... people would simply memorize instead of taking the time to figure it out. 😉
Well, I'll post mine because it is (of course) not perfect. But I think it's fairly good.
It's in the form of a stored procedutre that works on any number, rather than hard coded 100.
USE play -- my sandbox db
go
CREATE proc FIZZBUZZ (@limit int)
-- with RECOMPILE
-- recompile probably not needed unless called with low numbers like 0,1 as well as big numbers
as
-- proc to generate FIZZBUZZ results for numbers up to about 2 billion
if @limit < 0 return -- can't handle negative number of rows
DECLARE @cubelim int set @cubelim = 1+POWER(@limit,1.0/3.0);
-- rough number of rows needed in each componenet of a 3-way self-cross-join to achieve @limit in the result
with some_columns as -- as much of master.sys.allcolumns as we need, no more
(SELECT top (@cubelim) * from Master.sys.all_columns),
CteNums (J) as -- create Tally cte
(SELECT top (@limit) ROW_NUMBER() OVER (ORDER BY AC1.Object_ID) AS J
from some_columns AC1
CROSS JOIN some_columns AC2
CROSS JOIN some_columns AC3)
SELECT
-- J as number, /* the number being tested for dividability by 3 and/or by 5 */
-- uncomment the line above if J is wanted in the result set
CASE when (J%15 = 0) then 'FIZZBUZZ' -- multiple of 3 and of 5
when (J%5 = 0) then 'BUZZ'
when (J%3 = 0) then 'FIZZ'
else ltrim(str(J))
END as Number_or_Name
from cteNums
go
Tom
February 22, 2010 at 9:36 am
Steve Cullen (2/22/2010)
Wouldn't this be faster?
Want to test with 10mm rows and let us know?
February 22, 2010 at 9:39 am
Steve Jones - Editor (2/22/2010)
Steve Cullen (2/22/2010)
Wouldn't this be faster?Want to test with 10mm rows and let us know?
Too lazy 😛
February 22, 2010 at 9:39 am
So many fails...fizbuz? BizzBuzz?
No attention to the specification...don't worry gents, I'll hold the door for you 😛
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 22, 2010 at 9:41 am
GSquared (2/22/2010)
Just to annoy Jeff, here's my "ultimate version":
SET NOCOUNT ON;DECLARE @Table1 TABLE (
ID int IDENTITY PRIMARY KEY,
Number int);
DECLARE @Number INT;
SELECT @Number = 1;
WHILE @Number <= 100
BEGIN
INSERT INTO @Table1 (Number)
SELECT @Number
WHERE @Number NOT IN
(SELECT Number
FROM @Table1);
SELECT @Number = @Number + 1;
END;
DECLARE curNumbers CURSOR GLOBAL DYNAMIC FOR
SELECT DISTINCT Number
FROM @Table1
ORDER BY Number;
DECLARE @Number2 INT, @Number2String VARCHAR(max), @Msg VARCHAR(MAX);
OPEN curNumbers;
FETCH FIRST FROM curNumbers
INTO @Number2;
WHILE @@fetch_status = 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(3 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = CAST(@Number2 AS VARCHAR(MAX));
END;
ELSE
BEGIN
SELECT @Msg = 'Buzz'
END;
END;
ELSE
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = 'Bizz';
END;
ELSE
BEGIN
SELECT @Msg = 'BizzBuzz';
END;
END;
PRINT @Msg;
FETCH NEXT FROM curNumbers
INTO @Number2;
END;
CLOSE curNumbers;
DEALLOCATE curNumbers;
Geez Gus. Feeling like an instigator today?
Let me guess - this took you 5m02sec to write?
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
February 22, 2010 at 9:46 am
It boggles my mind to see how far I have to go to be competent in T-SQL. I read these columns for enlightenment but so much of it is so much higher that most things go over my head without a lot of research.
The one point I want to make, and I have seen it here, is that the person being tested is so far above the interviewer in skill that the interviewer and other current staff can't even understand the code. We had an employee who transferred into our group who wrote code so tight that DBAs complained that it was over their heads. They said that it could not be supported because they did not understand it. Granted it could have been documented better but this person thought that it was so obvious that documentation was redundant. They (and I am not one, I'm a lowly applications analyst/programmer using T-SQL and Crystal Reports exclusively) do not subscribe or browse these columns to advance their knowledge.
February 22, 2010 at 9:49 am
CirquedeSQLeil (2/22/2010)
GSquared (2/22/2010)
Just to annoy Jeff, here's my "ultimate version":
SET NOCOUNT ON;DECLARE @Table1 TABLE (
ID int IDENTITY PRIMARY KEY,
Number int);
DECLARE @Number INT;
SELECT @Number = 1;
WHILE @Number <= 100
BEGIN
INSERT INTO @Table1 (Number)
SELECT @Number
WHERE @Number NOT IN
(SELECT Number
FROM @Table1);
SELECT @Number = @Number + 1;
END;
DECLARE curNumbers CURSOR GLOBAL DYNAMIC FOR
SELECT DISTINCT Number
FROM @Table1
ORDER BY Number;
DECLARE @Number2 INT, @Number2String VARCHAR(max), @Msg VARCHAR(MAX);
OPEN curNumbers;
FETCH FIRST FROM curNumbers
INTO @Number2;
WHILE @@fetch_status = 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(3 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = CAST(@Number2 AS VARCHAR(MAX));
END;
ELSE
BEGIN
SELECT @Msg = 'Buzz'
END;
END;
ELSE
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = 'Bizz';
END;
ELSE
BEGIN
SELECT @Msg = 'BizzBuzz';
END;
END;
PRINT @Msg;
FETCH NEXT FROM curNumbers
INTO @Number2;
END;
CLOSE curNumbers;
DEALLOCATE curNumbers;
Geez Gus. Feeling like an instigator today?
Let me guess - this took you 5m02sec to write?
That sound you just heard was Jeff exploding 😀
February 22, 2010 at 9:54 am
dennis.oconnor (2/22/2010)
It boggles my mind to see how far I have to go to be competent in T-SQL. I read these columns for enlightenment but so much of it is so much higher that most things go over my head without a lot of research.The one point I want to make, and I have seen it here, is that the person being tested is so far above the interviewer in skill that the interviewer and other current staff can't even understand the code. We had an employee who transferred into our group who wrote code so tight that DBAs complained that it was over their heads. They said that it could not be supported because they did not understand it. Granted it could have been documented better but this person thought that it was so obvious that documentation was redundant. They (and I am not one, I'm a lowly applications analyst/programmer using T-SQL and Crystal Reports exclusively) do not subscribe or browse these columns to advance their knowledge.
The fact that you come here is testament to your bright future. We all had to start somewhere (except Jeff who clearly was delivered into this world this way).
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 22, 2010 at 10:04 am
GSquared (2/22/2010)
Just to annoy Jeff, here's my "ultimate version":
SOM! :w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 22, 2010 at 10:05 am
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
February 22, 2010 at 10:06 am
Gary Varga (2/22/2010)
dennis.oconnor (2/22/2010)
It boggles my mind to see how far I have to go to be competent in T-SQL. I read these columns for enlightenment but so much of it is so much higher that most things go over my head without a lot of research.The one point I want to make, and I have seen it here, is that the person being tested is so far above the interviewer in skill that the interviewer and other current staff can't even understand the code. We had an employee who transferred into our group who wrote code so tight that DBAs complained that it was over their heads. They said that it could not be supported because they did not understand it. Granted it could have been documented better but this person thought that it was so obvious that documentation was redundant. They (and I am not one, I'm a lowly applications analyst/programmer using T-SQL and Crystal Reports exclusively) do not subscribe or browse these columns to advance their knowledge.
The fact that you come here is testament to your bright future. We all had to start somewhere (except Jeff who clearly was delivered into this world this way).
Jeff is a set-based organism who sprang into multi-being at one instant. He is the Chosen One. Morpheus has said so.
---------------------------------------------------------
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."
February 22, 2010 at 10:20 am
CirquedeSQLeil (2/22/2010)
GSquared (2/22/2010)
Just to annoy Jeff, here's my "ultimate version":
SET NOCOUNT ON;DECLARE @Table1 TABLE (
ID int IDENTITY PRIMARY KEY,
Number int);
DECLARE @Number INT;
SELECT @Number = 1;
WHILE @Number <= 100
BEGIN
INSERT INTO @Table1 (Number)
SELECT @Number
WHERE @Number NOT IN
(SELECT Number
FROM @Table1);
SELECT @Number = @Number + 1;
END;
DECLARE curNumbers CURSOR GLOBAL DYNAMIC FOR
SELECT DISTINCT Number
FROM @Table1
ORDER BY Number;
DECLARE @Number2 INT, @Number2String VARCHAR(max), @Msg VARCHAR(MAX);
OPEN curNumbers;
FETCH FIRST FROM curNumbers
INTO @Number2;
WHILE @@fetch_status = 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(3 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = CAST(@Number2 AS VARCHAR(MAX));
END;
ELSE
BEGIN
SELECT @Msg = 'Buzz'
END;
END;
ELSE
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = 'Bizz';
END;
ELSE
BEGIN
SELECT @Msg = 'BizzBuzz';
END;
END;
PRINT @Msg;
FETCH NEXT FROM curNumbers
INTO @Number2;
END;
CLOSE curNumbers;
DEALLOCATE curNumbers;
Geez Gus. Feeling like an instigator today?
Let me guess - this took you 5m02sec to write?
Not sure how long it took to write. I had to look up how to declare a global cursor, since I've never bothered with one before. (Is it a good thing that I don't have the process for declaring cursors memorized?)
- 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
February 22, 2010 at 10:20 am
OK then let my chuck in my fifty cents.
I haven't had time to read all comments, so I don't know if a similar solution has yet been provided. I really would like some feedback, though, as to wether or not I am in the ball park.
I must admit that my first attempt was a while loop with an if...else, which took me about 3 minutes to rattle off. But after reading some of the comments about loops and going back to look at the execution plan I decided to put a bit more effort into this.
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) and trying master.dbo.spt_values (limited to a value of 2048).
The solution dawned on me while I was trying to populate a table variable like this:
insert into @table
select top 10000 ROW_NUMBER() over (order by sc1.name)
fromsys.columns sc1,
sys.columns sc2
I wanted to use the table variable in a second select with the case evaluation. And then I thought there has to be a way of doing this without any DDL or table variables etc...
I figured that ROW_NUMBER is the way to go and after a few minutes, this is what I came up with:
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
fromsys.columns sc1,
sys.columns sc2
Easily scaleable and, as far as I'm concerned, easily readable.
Wheter or not I would have come up with this in an interview, who knows?
I think the important point is that it was a process that started with a loop went on to a temp tally table, then a variable tally table (which was faster) and culminated in the final solution. 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.
Some feedback on this would be appreciated.
Thanks
Sean
Viewing 15 posts - 61 through 75 (of 363 total)
You must be logged in to reply to this topic. Login to reply