February 22, 2010 at 10:22 am
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
February 22, 2010 at 10:31 am
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."
February 22, 2010 at 10:45 am
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 100case
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
February 22, 2010 at 10:50 am
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 100case
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
February 22, 2010 at 10:54 am
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
February 22, 2010 at 10:55 am
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 100case
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
February 22, 2010 at 10:57 am
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?
February 22, 2010 at 10:59 am
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
February 22, 2010 at 11:57 am
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
February 22, 2010 at 12:02 pm
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
February 22, 2010 at 12:07 pm
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
February 22, 2010 at 12:13 pm
Sean-752587 (2/22/2010)
JasonAs 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
February 22, 2010 at 12:14 pm
Sean-752587 (2/22/2010)
Hi jcrawf02How 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
February 22, 2010 at 12:19 pm
WayneS (2/22/2010)
Sean-752587 (2/22/2010)
Hi jcrawf02How 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.
February 22, 2010 at 12:24 pm
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."
Viewing 15 posts - 76 through 90 (of 363 total)
You must be logged in to reply to this topic. Login to reply