SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The T-SQL Quiz


The T-SQL Quiz

Author
Message
mrsnake
mrsnake
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 604

I happened to have a UDF in my test box that returns a set of numbers, so it was very easy for me to put this together:

select case when n % 3 = 0 and n % 5 = 0 then 'BizzBuzz' when n % 3 = 0 then 'Bizz' when n % 5 = 0 then 'Buzz' else cast(n as varchar(10)) end from Numbers(1,100)

The UDF is something I found on Mr Steve Kass's home page. In turn, he gives credit to Mr Itzik Ben-Gan.

create function Numbers(
@from as bigint,
@to as bigint
) returns table with schemabinding as return
with t0(n) as (
select 1 union all select 1
 , t1(n) as (
select 1 from t0 as a, t0 as b
 , t2(n) as (
select 1 from t1 as a, t1 as b
 , t3(n) as (
select 1 from t2 as a, t2 as b
 , t4(n) as (
select 1 from t3 as a, t3 as b
 , t5(n) as (
select 1 from t4 as a, t4 as b
 , Numbers(n) as (
select row_number() over (order by n) as n
from t5
 
select @from + n - 1 as n
from Numbers
where n <= @to - @from + 1

Try it also with a million numbers. It is pretty fast!





Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26100 Visits: 12483
Jeff, a little note:
there was no requirement for proper commenting, there was a requirement for 2 min solution.
Cannot say about anybody else, but my typing would not give me a chance to fit the time requirement if I would start typing comments.

And for scalabale solution I would go with Numbers table as well, but for that request spt_values got more than enough numbers. So why waste time on it, if total number could be limited by some business logic.
SwePeso
SwePeso
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9827 Visits: 3433
declare @ int set @=1while @<101begin print ISNULL(NULLIF(CASE WHEN @%3=0THEN'Fizz'ELSE''END+CASE WHEN @%5=0THEN'Buzz'ELSE''END,''),@)set @=@+1 end


N 56°04'39.16"
E 12°55'05.25"
SwePeso
SwePeso
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9827 Visits: 3433

More here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79902&whichpage=1




N 56°04'39.16"
E 12°55'05.25"
jeff preston-223188
jeff preston-223188
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

Deadline missed, budget exceeded, client unhappy.

The problem was simple, the solution was not.


duanxn-306048
duanxn-306048
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

Ok, fed up reading the thread. I would have failed all the interviewers just simplely in comparing the following two code pieces:

--====== A ========

Case when @i % 3 = 0 and @i % 5 = 0 then 'BizzBuzz'

when @i % 3 = 0 then 'Bizz'

when @i % 5 = 0 then 'Buzz'

else Str(@i)

End

--====== B ========

Case when @i % 5 = 0

case when @i % 3 = 0 then 'BizzBuzz' else 'Buzz' end

when @i % 3 = 0 then 'Bizz'

else Str(@i)

End

The method B will save half of the modular calculation!


George Ndicu
George Ndicu
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 2
Hi All
Below is my entry

declare @count int, @message nvarchar(10), @message1 nvarchar(10), @message2 nvarchar(10)
set @count = 1


while @count <= 100
BEGIN
set @message = '' set @message1 = '' set @message2 = ''
IF @count%3 = 0
set @message1 = 'BIZZ'
IF @count%5 = 0
set @message2 = 'BUZZ'

set @message = @message1 + @message2
select @count, @message

set @count = @count + 1

END

sorry i have to get back to work so i did not check how quickly it run
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219622 Visits: 42002

Heh... you read into it, ol' friend... no where in the post does there say anything about a time limit.

[Edit] Ok, I see where you came up with the 2 minute limit... wasn't in Grant's article but in one of the links provided...

If there were a 2 minute time limit for such a thing and it was during an interview, then yes... skip the comments 'cause that's not what they're looking for, but consider finding a job somewhere else because they've just set the stage for how they're gonna operate all the time.

So far as the total number being limited by some business logic... how many times has that happened to you only to have that business logic change?

I'll say it again, write performance enabled, scalable, documented code even for the simplest things... unless you really enjoy treading water



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219622 Visits: 42002

Heh... looking at it the wrong way...

Original budget/deadline was wrong because you didn't bid right... Client necessarily changes scale... wants to know why idiots wrote code that wasn't scalable... all future contracts lost because client thinks people who don't have their best interest at heart wrote the code.

And, wanna tell be why the following would take more than 2 minutes? It's still documented , scalable, nasty fast, and it still uses setbased thinking... customer happy, inteviewer happy, boss happy, and my peers don't get the work because I had the for-thought to write code anticipating a change...

--===== Limit the number of rows to be built
SET ROWCOUNT 1000000

--===== Create and populate the table on the fly
SELECT IDENTITY(INT,1,1) AS RowNum,
CAST(NULL AS VARCHAR(10)) AS DesiredResult
INTO #Nums
FROM Master.dbo.SysColumns sc1 WITH (NOLOCK),
Master.dbo.SysColumns sc2 WITH (NOLOCK)

--===== Restore the number of rows to return to normal
-- Note: Can replace with TOP @variable in SQL 2k5
SET ROWCOUNT 0

--===== Produce the desired results according to the
-- requirements
SELECT CASE
WHEN RowNum % 15 = 0 THEN 'BizzBuzz' --Divisible by 15
WHEN RowNum % 3 = 0 THEN 'Bizz' --Divisible by 3
WHEN RowNum % 5 = 0 THEN 'Buzz' --Divisible by 5
ELSE CAST(RowNum AS VARCHAR(10))
END AS DesiredResult
FROM #Nums
ORDER BY RowNum



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219622 Visits: 42002
And, you left out a THEN necessary to make it work And, although you think B will save half of the modular calculation, the code runs at the same speed as what you have for A.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search