SQLServerCentral apologizes and you can win a book

  • bhw0006 48811 (12/9/2013)


    This is awesome ya'll are doing this

    if object_id('tempdb..#table') is not null drop table #table

    go

    select first_name, last_name, age

    into #table

    from (

    select 'Chuck' as first_name, 'Norris' as last_name, '32' as age union all

    select 'Chuck' as first_name, 'Norris' as last_name, '32' as age union all

    select 'Jean Claude' as first_name, 'Van Damme' as last_name, '32' as age union all

    select 'Bruce' as first_name, 'Willis' as last_name, '32' as age union all

    select 'Bruce' as first_name, 'Willis' as last_name, '32' as age union all

    select 'Jean Claude' as first_name, 'Van Damme' as last_name, '32' as age union all

    select 'Jean Claude' as first_name, 'Van Damme' as last_name, '32' as age union all

    select 'Chuck' as first_name, 'Norris' as last_name, '32' as age

    ) as d1

    alter table #table add id int identity(1,1)

    select

    row_number() over(partition by first_name, last_name, age order by last_name) as r,

    first_name, last_name, age

    from

    #table

    delete q from (

    select

    row_number() over(partition by first_name, last_name, age order by last_name) as r,

    first_name, last_name, age

    from

    #table

    ) as q

    where

    r > 1

    select

    row_number() over(partition by first_name, last_name, age order by last_name) as r,

    first_name, last_name, age

    from

    #table

    Welcome to SSC and well done. You should take a look at this article if you can, it shows a faster, better way to generate a sequence of numbers. Below is a quick test I put together with 1,000,000 rows; the first method uses the Recursive Common Table Expression that you posted and the second one uses a tally table (look up "table of numbers" if you win the Ben Gan book 😉 ).

    SET NOCOUNT ON;

    SET STATISTICS TIME ON;

    GO

    -- Recursive CTE

    WITH Numbers AS

    (SELECT 1 AS Nr

    UNION ALL

    SELECT Nr + 1

    FROM Numbers

    WHERE Nr < 1000000

    )

    SELECT

    Nr,

    CASE

    WHEN Nr >= 5

    THEN AVG(cast(Nr as Money)) OVER(ORDER BY Nr ROWS BETWEEN 4 PRECEDING and CURRENT ROW)

    ELSE 0

    END AS RollingAvg

    FROM Numbers

    ORDER BY 1

    OPTION (MAXRECURSION 0);

    -- Tally table

    WITH

    e1(Nr) AS--10

    (SELECT Nr

    FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(Nr)

    ),

    e2(Nr) AS--100

    (SELECT (0)

    FROM e1 a CROSS JOIN e1 b),

    e3(Nr) AS--1000

    (SELECT (0)

    FROM e2 a CROSS JOIN e1 b),

    Numbers(Nr) AS--1,000,000

    (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))

    FROM e3 a CROSS JOIN e3 b

    )

    SELECT

    Nr,

    CASE

    WHEN Nr >= 5

    THEN AVG(cast(Nr as Money)) OVER(ORDER BY Nr ROWS BETWEEN 4 PRECEDING and CURRENT ROW)

    ELSE 0

    END AS RollingAvg

    FROM Numbers

    SET STATISTICS TIME OFF

    GO

    Results:

    --recursive CTE--

    SQL Server Execution Times:

    CPU time = 10358 ms, elapsed time = 15802 ms.

    --tally table--

    SQL Server Execution Times:

    CPU time = 2496 ms, elapsed time = 6668 ms.

    The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thanks SSC! Here is a query that runs against the 2012 Adventureworks database:

    SELECT SalesOrderID

    , SalesPersonID

    , OrderDate

    , SubTotal

    , ROW_NUMBER() OVER (PARTITION BY OrderDate ORDER BY SubTotal DESC) AS OrderRankByDay

    , DENSE_RANK() OVER (PARTITION BY OrderDate ORDER BY SubTotal DESC) AS DenseOrderRankByDay

    , CUME_DIST() OVER (PARTITION BY OrderDate ORDER BY SubTotal DESC) AS CumDistByDay

    , LAG(SubTotal, 1, NULL) OVER (PARTITION BY OrderDate ORDER BY SubTotal DESC) AS PreviousSubtotal

    FROM Sales.SalesOrderHeader

  • Plagirism is not a good thing.

    below is the code to get the number of days left to christmas.

    with DaysTillXMas as

    (

    select cast(convert(date, getdate()) as datetime) as RemDates

    union all

    select (RemDates + 1) from DaysTillXMas where (RemDates + 1) < '2013-12-25'

    )

    select row_number() over(order by (select 1)) as DatesCount, RemDates from DaysTillXMas OPTION (MAXRECURSION 0)

  • I admit that I did have to consult the internet for some help on understanding what OVER does. I should play around with using this more.

    I am a database administrator for a ticketing system and put together this query for number of days that a ticket has been opened, and comparing it to the group that was assigned to it:

    SELECT Ticket#,

    CONVERT(FLOAT(closedate-opendate) / (24*60*60)) AS ElapsedTimeOpen,

    SUM(CONVERT(FLOAT(closedate-opendate) / (24*60*60))) OVER (partition by Group) AS SumElapsedTimeOpen,

    AVG(CONVERT(FLOAT(closedate-opendate) / (24*60*60))) OVER (partition by Group) AS AverageElapsedTimeOpen,

    Group

    FROM tickets

    WHERE closedate is not null

    AND DATEADD(ss,opendate-14400, '01/01/1970') > getdate()-7 -- opened no later than a week ago

    AND (closedate-opendate) > 86400 -- was opened for at least 1 day

    ORDER BY opendate DESC

  • It is nice to see that SQLServerCentral is supporting the author after the mishap.

    I use the following query to get the first item in a list. I assume it has already been posted.

    SELECT a.first_name, a.book_id

    FROM (

    SELECT ROW_NUMBER() OVER(PARTITION BY a.user_id ORDER BY b.creation_date DESC) line, a.first_name, b.book_id

    FROM a

    INNER JOIN [books] b ON a.user_id = b.user_id

    ) a

    WHERE a.line = 1

  • Using ROW_NUMBER() for display purposes.

    CREATE TABLE #Relationships

    (

    IDINT

    ,NameVARCHAR(50)

    ,ParentIDINT

    )

    INSERT INTO #Relationships(ID, Name, ParentID)

    SELECT 1, 'Walt', NULL

    UNION ALL SELECT 2, 'Frank', NULL

    UNION ALL SELECT 3, 'Steven', 1

    UNION ALL SELECT 4, 'Selma', 1

    UNION ALL SELECT 5, 'Heidi', NULL

    UNION ALL SELECT 6, 'Stephanie', NULL

    UNION ALL SELECT 7, 'Joyce', 5

    UNION ALL SELECT 8, 'George', 5

    UNION ALL SELECT 9, 'Rebecca', 5

    UNION ALL SELECT 10, 'Nathan', 3

    UNION ALL SELECT 11, 'Mike', 3

    UNION ALL SELECT 12, 'Charles', 3

    ;WITH Hierarchy AS

    (

    SELECT R2.NameAS Parent

    , R1.NameAS Child

    , ROW_NUMBER() OVER (PARTITION BY R2.ID ORDER BY R1.ID) AS RowNum

    FROM #Relationships R1

    JOIN #Relationships R2 ON R1.ParentID = R2.ID

    )

    -- only display parent's name once

    SELECT CASE H.RowNum WHEN 1 THEN H.Parent ELSE '' END AS Parent

    , H.Child

    FROM Hierarchy H

    DROP TABLE #Relationships

  • Plagiarism of any kind is bad, but i am sure none of us here want to get involved in that kind of practice. It must have got published on this forum by mistake and Mr. Ben-Gan will forgive us.

    SQl is a very expressive and obvious language for all database developers. Below Code demonstrate use of Grouping to get aggregate on few columns and then get Rownumber for Output rows .

    [Code]

    SELECT

    (ROW_NUMBER() OVER (ORDER BY DeliveryLocation, PONumber, PODate, Supplier, DeliveryDate, StockItem)) AS IRowNumber,

    (CASE WHEN GROUPING(DeliveryLocation) = 0 THEN DeliveryLocation ELSE 'Buyer Total' END) AS [Buyer],

    (CASE WHEN GROUPING(PONumber) = 0 THEN PONumber ELSE 'Document Total' END) AS [Number],

    (CASE WHEN GROUPING(PODate) = 0 THEN CONVERT(VARCHAR(10), PODate, 103) ELSE 'Document Date Total' END) AS [PO Date],

    (CASE WHEN GROUPING(Supplier) = 0 THEN Supplier ELSE 'Supplier Total' END) AS [Supplier],

    (CASE WHEN GROUPING(DeliveryDate) = 0 THEN CONVERT(VARCHAR(10), DeliveryDate, 103) ELSE 'Delivery Date' END) AS [Delivery Date],

    (CASE WHEN GROUPING(StockItem) = 0 THEN StockItem ELSE 'Stock Item' END) AS [Stock Item],

    SUM(OrderedQty) AS [Ordered Qty], SUM(OrderedValue) AS [Ordered Value],

    SUM(ReceivedQty) AS [Received Qty], SUM(ReceivedValue) AS [Received Value],

    SUM(PendingQty) AS [Pending Qty], SUM(PendingValue) AS [Pending Value]

    FROM PURCASEORDERDETAIL

    GROUP BY DeliveryLocation, PONumber, PODate, Supplier, DeliveryDate, StockItem

    WITH ROLLUP

    [/Code]

  • The book looks great. Buy it if you don't win!

    /*--------------------------------------

    Import Excel worksheet with row numbers

    2013-12-11 Jeff Brooks

    Output columns are RowNum, F1, F2, F3,...

    */--------------------------------------

    IF OBJECT_ID('tempdb..#ExcelImport') IS NOT NULL DROP TABLE #ExcelImport

    SELECT

    RowNum = ROW_NUMBER() OVER(ORDER BY GETDATE())

    , *

    INTO #ExcelImport

    FROM

    OPENROWSET(

    'Microsoft.ACE.OLEDB.12.0'

    , 'Excel 12.0 XML;HDR=NO;Database=C:\Code\TestFiles\ExecTest.xlsx;'

    , 'SELECT * FROM [TestSheet1$]'

    );

    SELECT *

    FROM #ExcelImport

  • jk_in_San_Diego (12/5/2013)


    Once upon a time, in the dark ages of printed textbooks and such, plagarism was not only frowned upon, it was cause for immediate dismissal or expulsion from university.

    To claim the work of another as your own is at best fraud and at worst theft.

    Always cite your sources but use them as sources of information, not the sources of your content!

    Hear, hear!

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Great book!

    WITH TEMP

    AS

    (

    SELECT

    row_number() over (order by book_id) as ID

    ,BOOK_DETAIL_ID

    FROMBOOKS

    )

    SELECT *

    FROM TEMP

  • First time playing around with this.

    Looks like something I would use from time to time.

    USE AdventureWorksDW2008R2

    go

    with temp_tbl

    as (

    select

    ROW_NUMBER() over (order by PostalCode desc) as Row_Number

    ,GeographyKey

    ,City

    ,StateProvinceCode

    ,StateProvinceName

    ,CountryRegionCode

    ,EnglishCountryRegionName

    ,PostalCode

    ,SalesTerritoryKey

    from

    dbo.DimGeography

    where

    CountryRegionCode = 'US'

    and StateProvinceCode = 'MN')

    SELECT *

    FROM temp_tbl

    where Row_Number = 3

  • Last evening I went to a Pacific NW SQL user group meeting. The guest speaker was Itzik Ben-Gan.

    Before that:

    I opened the “Apologies…” email last week from SQLServerCentral.com (very catchy title, by the way, as I only open 30-60% of these depending on personal bandwidth). I thought, “Hmmm, this name sounds familiar.” Sure enough, he was the speaker that I had just read about in the PNWSQL email, and the previous month’s presenter had spoken very highly of him. I had already planned on going, but maybe I could chat with him after the presentation and get an inside track on winning a new book. Possibly I could get an interesting quote to share with Steve Jones and all of the contributors and followers of the website.

    So then went into research mode. Who was Itzik? What was he about? What has he done? How could I appeal to him? I saw one of his other books on Amazon, Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012. When I saw that one of the co-authors was Ron Talmage the president of PNWSQL, I bought it. What the heck, I’d been feeling it is time to update my certs from SQL 2000. I also felt that it would be better if I showed up with a book “in hand”. I could ask for an autograph after the presentation which could create the opportunity to ask Itzik about the “unintended plagiarism” on SQLServerCentral.com, the reaction by Steve Jones and the “win a book written by Itzik” offer.

    So I arrived at the presentation with high expectation. I was not disappointed. The title of the presentation was, “Creative Uses of the APPLY Operator.” The room was fairly packed. There were more people there than the prior three months combined.

    Itzik is an engaging and smooth presenter. He can manage the presentation devices, write code, present theory, and have it all make sense faster than I could take notes. He has the charisma of a movie star, a cool accent and the enthusiasm of a child at Christmas. The presentation reminded me of watching Roberto Benigni accepting his Oscar for Best Actor in his 1997 movie, Life Is Beautiful (look it up on YouTube.) I learned more in the first 10 minutes than I could process, and I had 65 minutes to go.

    By the way, the much easier way to go would have been to study his previous work. He gave out 8 or so books during the course of the evening. If I was better prepared, I could have answered a question correctly and wouldn’t have to write this essay.

    I may have been star struck.

    By the end of the presentation, I walked up to him. I actually opened my Amazon box in front of him. My comment was, “I brought my own book, would you sign it for me.” He was very accessible and charming. He wrote, “Happy Querying!” and signed my book.

    During this time, I asked him if he had heard about the kerfuffle on SQLServerCentral.com. He said he had, and had received an email from Steve Jones.

    Yet, I think I caught him off guard. I think he wanted questions about the “Apply Operator”. I said, “I was hoping for some words that I could use that would help me “cinch” the “win a book” contest. He responded enthusiastically, “use the code that I presented that included the sum(sum(val)), that is really good. That will earn you a free book.”

    Unfortunately, by that point in the presentation, I was already a page behind and had given up on copying and instead chose to enjoy the show.

    I tried one more probing question, “I was hoping for a quote.” Again, he gave me a curious look and politely said, “I’m drawing a blank.”

    I felt as though I had failed in my mission. I was hoping to have something interesting to quote, and I was even hoping to create a memorable moment. Yet, I started feeling as if I were being impolite and/or boorish. I was disappointed in my attempt and somewhat frustrated with myself.

    I turned to Ron Talmage to have him sign my book as well. I was juggling my Amazon box, SQL book, notepad and pen. Somehow the SQL book goes forcefully flying ten feet across the table and past Itzik. He reached over to pick up the book. As he hands it to me, he exuberantly quips, “Here’s a quote for you. Say that you tried to kill me with my own book.”

    So, I wish I had more of Itzik’s latest code to share with you (he offered), but I feel the time crunch of posting this to the forum.

    Here is some code that I use on my personal database project. Basically, I have a horseracing database that I use for research. It took me a while to figure out how to compare horses within each race. This code allows me to create a view from one of my tables. I can then query the view to look for the top ranked horse within a race. I also use it to compare previous similar races to an upcoming race and determine how “deep” I may need to go to find today’s winner. For example, in historical races like today’s first race 10 of the 12 winners were in the top 2 (or 6) ranked Prime Power.

    Steve, Thank you for the opportunity and space.

    Create View [dbo].[vL2RPrime] AS

    Select

    [Track]

    ,[Date]

    ,[Year]

    ,[Month]

    ,[Day]

    ,[Race#]

    ,[ProgramNumber]

    ,[PostPosition]

    ,[HorseName]

    ,[TodaysTrainer]

    ,[PrimePower]

    ,RANK() OVER

    (PARTITION BY (DTR) ORDER BY PrimePower DESC) AS RankPrime

    FROM vLSIV2ndWin

  • kennethrbell:

    Really nice post about your attendance at Itzik's presentation. Although we're all a bunch of techies, it's nice to read a story that touches upon ourselves as people, even when muddling through a techie moment. I was fortunate enough to attend one of Itzik's presentation a few years ago in Orlando. He has such an ease when presenting. I was amazed at how simple and elegant his code would be when illustrating a concept -- often by just querying the Pubs database.

    How cool for you.

    Thanks for the story,

    --pete

  • kennethrbell (12/12/2013)


    Steve, Thank you for the opportunity and space.

    You are welcome, and thanks for the note.

  • My apologies, but the contest is over. I picked the winners this morning.

    I'll post something with names tomorrow or Monday.

Viewing 15 posts - 271 through 285 (of 287 total)

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