December 16, 2016 at 2:11 am
Comments posted to this topic are about the item T-SQL implementation of FizzBuzz
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 20, 2016 at 9:39 am
Nice solution.
One less line of code: 🙂
SELECT
CASE WHEN [x].[n] % 15 = 0 THEN 'FizzBuzz'
WHEN [x].[n] % 3 = 0 THEN 'Fizz'
WHEN [x].[n] % 5 = 0 THEN 'Buzz'
ELSE CAST([x].[n] AS VARCHAR(9))
END AS [p]
FROM
(
SELECT
ROW_NUMBER() OVER ( ORDER BY [id] ) AS [n]
FROM
[sys].[sysobjects]
) [x]
WHERE
[x].[n] <= 100
ORDER BY
[x].[n];
gsc_dba
December 28, 2016 at 6:02 am
Never heard of that interview question before.
December 28, 2016 at 9:56 am
If you are going for fewest number of characters in the exercise you can save one more with:
WHERE n < 101
December 28, 2016 at 1:11 pm
Iwas Bornready (12/28/2016)
Never heard of that interview question before.
I've never heard it or asked it during an actual interview. Someone else on the team had suggested using it once for interviews, but when I saw their baseline solution was a cursor with 30 lines of code, I wanted something better, so I came up with this.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 30, 2016 at 10:54 am
One more version, using a "single" select statement 😉
SELECT TOP 100
CASE
WHEN ROW_NUMBER() OVER (ORDER BY object_id) % 15 = 0 THEN 'FizzBuzz'
WHEN ROW_NUMBER() OVER (ORDER BY object_id) % 3 = 0 THEN 'Fizz'
WHEN ROW_NUMBER() OVER (ORDER BY object_id) % 5 = 0 THEN 'Buzz'
ELSE CAST( ROW_NUMBER() OVER (ORDER BY object_id) AS varchar)
END
FROM
sys.objects
January 3, 2017 at 9:24 am
In an empty database I only got 97 numbers from sys.objects.
WITH x(y) AS (SELECT top (100) ROW_NUMBER() OVER (ORDER BY id) FROM sys.syscolumns s)
SELECT CASE WHEN y % 15 = 0 THEN 'FizzBuzz' WHEN y % 3 = 0 THEN 'Fizz' WHEN y % 5 = 0 THEN 'Buzz' ELSE CAST(y AS varchar) END
FROM x
January 3, 2017 at 2:14 pm
Non set-based solution.
Doesn't depend on selecting from a system table that has the desired number of rows. (What if it was for numbers 1-1000000?)
Preserves the integer datatype of the numbers. (If that is needed.)
DECLARE @Count INT = 0
WHILE @Count < 100
BEGIN
SET @Count = @Count + 1
IF (@Count%15 = 0)
PRINT 'FizzBuzz'
ELSE IF (@Count%3 = 0)
PRINT 'Fizz'
ELSE IF (@Count%5=0)
PRINT 'Buzz'
ELSE
PRINT @Count
END
January 3, 2017 at 2:33 pm
Simple. Just add a few cross joins for however many you need.
WITH x(y) AS (SELECT top (1000) ROW_NUMBER() OVER (ORDER BY s.object_id) FROM sys.objects s CROSS JOIN sys.objects t)
SELECT CASE WHEN y % 15 = 0 THEN 'FizzBuzz' WHEN y % 3 = 0 THEN 'Fizz' WHEN y % 5 = 0 THEN 'Buzz' ELSE CAST(y AS varchar) END
FROM x
January 5, 2017 at 7:53 am
Similar in concept to Oracle [DUAL] table, it would be useful if SQL Server had a system tally table, perhaps containing only a single INT column with rows from -2,147,483,648 to 2,147,483,647. Currently, a lot of debate is centered around how best to implement such a thing on the fly.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 5, 2017 at 8:21 am
I currently use a table function to generate a range of numbers for joining to queries. I find it is easy for others to understand and is fast enough for everything we do. It includes an offset parameter to generate any range you may need. The code is below.
Since sql server 2012 introduced Sequence objects which are brilliant by the way, I've been wondering if a Sequence may be the way to go.
-- return range of rows for ss2012 or higher
IF OBJECT_ID('dbo.GetOneMillionNumbers', 'IF') IS NOT NULL DROP FUNCTION dbo.GetOneMillionNumbers;
GO
CREATE FUNCTION dbo.GetOneMillionNumbers(@count AS int, @offset int)
RETURNS TABLE
AS
RETURN
(
WITH x (y)
AS
(
SELECT n1.na
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n1 (na)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n2 (na)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n3 (na)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n4 (na)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n5 (na)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n6 (na)
)
SELECT top (@count)
ROW_NUMBER() OVER(ORDER by (SELECT NULL)) + @offset AS MySequence
from x
);
GO
-- SELECT * FROM dbo.GetOneMillionNumbers(775, 1000);
-- SELECT max(MySequence) FROM dbo.GetOneMillionNumbers(1000000, -1000001);
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply