SQLServerCentral apologizes and you can win a book

  • Honorable and classy move SQL Server Central. Too much plagiarism on the internet as people try to bolster their image. Calling them out like this has to happen more often.

  • WINDOW FUNCTION ARE EASY TO USE TO PERFORM OPERATIONS ON SET OF ROWS

    CREATE TABLE #SOURCE ( ID INT, NAME VARCHAR(50))

    INSERT INTO #SOURCE

    VALUES (1, 'ABC'), (3,'PQR' ), (4, 'XYZ')

    SELECT ID,NAME,ROW_NUMBER() OVER (ORDER BY ID) AS ROW_NO FROM #SOURCE

  • I just wanted to Kudos to the SQL ServerCentral folks for the quick action and response to their unfortunate mishap. Class Act!

  • Well done on the quick resolution to the problem.

    I've always felt that there's an openness to this site, that welcomes input from anyone, and often the discussions below the article contain more information and sometimes corrections without criticism of the original author.

    As an inexperienced SQL user i could really use a copy of the book, and I don't feel that I can contribute a useful example of original code but here's one that I use:

    SELECT

    DENSE_RANK() OVER(PARTITION BY Date, ItemCode, Batch ORDER BY TimePrinted) AS IssueNumber

    gets an issue number for recipes that have been issued multiple times. I usually use ORDER BY TimePrinted DESC because I'm only interested in the most recent copy, and that will always be IssueNumber = 1

    Edited because I accidentally posted it before i was ready!

  • Most of the examples so far have demonstrated the OVER clause used with aggregate (MIN, AVG, COUNT, etc) functions, or with ranking (ROW_NUMBER, DENSE_RANK etc) functions. In SQL2012, the functionality was extended to analytical functions too, such as LAG and LEAD.

    The best use for these is the ability to reference previous or next rows, without the need for a self-join, which was the main method prior to SQL2012.

    So let's set up some test data

    declare @Sales table

    (

    SalesYear int,

    SalesQuarter int,

    SalesAmount money

    )

    insert into @Sales select 2011,1,5000.00

    insert into @Sales select 2011,2,2000.00

    insert into @Sales select 2011,3,6000.00

    insert into @Sales select 2011,4,7000.00

    insert into @Sales select 2012,1,1000.00

    insert into @Sales select 2012,2,2500.00

    insert into @Sales select 2012,3,1800.00

    insert into @Sales select 2012,4,5000.00

    In it's simplest form, the LAG and LEAD functions give the previous and next values. The parameters in lag(SalesAmount,1,0) say that we want to go back 1 offset, and use 0 as the default value if there is no value at that offset. Otherwise you'd get NULL

    As per other OVER() windowing functions, you can use the PARTITION clause to limit the window, so for example if we wanted the same as above but only get the previous values where they are in the same SalesYear, we can do this.

    select

    SalesYear,

    SalesQuarter,

    SalesAmount,

    PrevSalesAmount = lag(SalesAmount,1,0)over(order by SalesYear, SalesQuarter),

    NextSalesAmount = lead(SalesAmount,1,0)over(order by SalesYear, SalesQuarter)

    from @Sales

    SalesYear SalesQuarter SalesAmount PrevSalesAmount NextSalesAmount

    ----------- ------------ --------------------- --------------------- -----------------

    2011 1 5000.00 0.00 2000.00

    2011 2 2000.00 5000.00 6000.00

    2011 3 6000.00 2000.00 7000.00

    2011 4 7000.00 6000.00 1000.00

    2012 1 1000.00 7000.00 2500.00

    2012 2 2500.00 1000.00 1800.00

    2012 3 1800.00 2500.00 5000.00

    2012 4 5000.00 1800.00 0.00

    (8 row(s) affected)

    select

    SalesYear,

    SalesQuarter,

    SalesAmount,

    PrevSalesAmount = lag(SalesAmount,1,0)over(partition by SalesYear

    order by SalesYear, SalesQuarter)

    from @Sales

    SalesYear SalesQuarter SalesAmount PrevSalesAmount

    ----------- ------------ --------------------- ---------------------

    2011 1 5000.00 0.00

    2011 2 2000.00 5000.00

    2011 3 6000.00 2000.00

    2011 4 7000.00 6000.00

    2012 1 1000.00 0.00

    2012 2 2500.00 1000.00

    2012 3 1800.00 2500.00

    2012 4 5000.00 1800.00

    (8 row(s) affected)

  • I was sorry to hear about the plagiarism that occurred on the site, but SQL Central is handling it in a very gracious way. I took the opportunity to learn a little bit about the OVER clause in TSQL. Here is an example query:

    SELECT p.FirstName, p.LastName

    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS "Row Number"

    ,s.SalesYTD, a.PostalCode

    FROM Sales.SalesPerson AS s

    INNER JOIN Person.Person AS p

    ON s.BusinessEntityID = p.BusinessEntityID

    INNER JOIN Person.Address AS a

    ON a.AddressID = p.BusinessEntityID

    WHERE TerritoryID IS NOT NULL

    AND SalesYTD <> 0;

    GO

  • I like Windows function, it just makes complex task simple. I use them all the time, I also wrote a column a while ago which explains all windows function available in SQL Server:

    http://searchsqlserver.techtarget.com/tip/T-SQL-analytic-functions-in-SQL-Server-can-help-solve-problems-quickly

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com
  • One thing most people don't realize is that quoting an author's work, and attributing it properly, might not be plagiarism but it is still copyright infringement. Microsoft gives a certain amount of latitidue in allowing us to quote BOL to each other to help the community solve problems. Yet if they caught us reciting whole "chapters" of BOL in articles, I bet they'd come down on us like a load of bricks.

    I believe people who take things like this whole sale from others' works just don't understand the subject and want to look smart without realizing the consequences to their own reputations. Or maybe they don't care that they will lose out on so many career opportunites. It's a shame, really.

    I was going to give an example of OVER() using code I've actually put into production, but I can't find it at the moment. If I find it later, I'll add it to this post.

    Found it! It's a double, non-recursive CTE.

    With OrderDetail AS

    (select dod.Key, dod.Num, dod.LastName, isnull(dod.FirstName,'') as FirstName,

    ds.SellerID, dt.FullDate, dod.Code, dod.Type,

    Row_Number() Over (Order by dod.Key) as MyOrder1

    from dbo.DimOrderDetail dod

    join dbo.DimSeller ds

    on dod.SellerKey = ds.SellerKey

    join dbo.DimTime dt

    on dod.EffDt = dt.TimeKey

    where dod.Num is NOT NULL),

    Orders AS

    (Select goi.No as Num, od.Key, goi.Type, goi.LastName, goi.FirstName,

    goi.SellerID, goi.StDt, goi.Type2, goi.Code, goi.Term, goi.OBAmt, goi.IRAmt,

    goi.Period, goi.IRB, goi.IIAmt, goi.Exp, goi.Cur, fo.CKey,

    Row_Number() Over (Order by od.Key) as MyOrder2

    from OrderDetail od

    join REPORTSERVER.MyDB.dbo.vDBA_GetOrderInfo goi

    on od.Num = goi.No

    and od.LastName = goi.LastName

    and od.FirstName = goi.FirstName

    and od.CovCD = goi.Type

    and od.InsCD = goi.Code

    and od.SellerID = goi.SellerID

    and od.FullDate = goi.StDt

    join dbo.FactOrders fo

    on od.Key = fo.Key)

    Insert into dbo.Staging (Key, Type, Code, IRAmt, OBAmt, Term, Period, IRB,

    IIAmt, Exp, Cur, StartDate, TimeKey, CKey)

    (Select Key, Type, Code, IRAmt, OBAmt, Term, Period, IRB, IIAmt, Exp, Cur,

    @TimeKey as StartDate, @TimeKey as TimeKey, CKey

    from Orders);

    EDIT: Scrubbed column names.

    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.

  • SELECT * FROM (

    SELECT NTILE(4) OVER ( PARTITION BY gt.SubGroup ORDER BY gt.RowID ) nt, *

    FROM dbo.SomeRandomTable gt)id

    WHERE id.nt=2

    This is an example where you have a table with RowID (identity column) and a secondary grouping column.

    1,1

    2,1

    3,2

    4,5

    ...

    The NTILE function is set to split the result set into 4 groups. (Obviously this works best if each of your subgroups contains at least 4 rows of data)

    Wrapping the whole statement in an additional select, lets you use the NTILE Function column and pick all the set data from each sub group that falls within the 25th to 50th percentile of the group.

    thanks

    Steve

  • Very simple example of how to use the OVER clause:

    SELECT

    Year(OrderDate),

    ROW_NUMBER() over(Partition By Year(OrderDate)order by OrderDate) as RowNum,

    *

    FROM [Northwind].[dbo].[Orders]

    ORDER BY Year(OrderDate), RowNum

    😉

  • Glad to know that SSC is honest.

    Im more of a .net developer, but i do have some sql skills. Here's my query that I used to find identify duplicate records by name and address.

    select DENSE_RANK() over (order by upper(name), upper([address])) GroupID,

    p.id ProviderID, npi, name, Name_First, Name_Last, [address], city, [state], zipcode

    into #Provs

    from wdls_providers p join WDLS_projectProviders pjp on p.id=pjp.providerID

    join WDLS_groupProviders gp on p.id=gp.providerID

    where Name is not null and ltrim(rtrim(name))<>'' and [address] is not null and ltrim(rtrim([address]))<>'' and

    pjp.projectID=@projectID and gp.groupID=@groupID

  • While plagiarizing is a no-no, and was quickly acknowledged and the offending article removed, it certainly helped me to re-discover the Windows functions especially the use of the OVER clause. I applaud SQLServerCentral for their honesty and forthright correction.

  • SELECT e.ID,

    COALESCE(e.ParentEstimateId, e.ID) AS EstimateNumber,

    CAST(ROW_NUMBER() OVER (PARTITION BY COALESCE(e.ParentEstimateId, e.ID) ORDER BY COALESCE(e.ParentEstimateId, e.ID), e.ID) AS int) AS RowNumber

    FROM dbo.Estimate e

    INNER JOIN CTE_MostlyNonConversionParentEstimates cte ON COALESCE(e.ParentEstimateID, e.ID) = cte.ParentEstimateId

    I don't use the windowing functions often, but there are those cases where it's a really great solution.

    Rob

  • I'm just really impressed by the reaction that this site had to content that was lifted without attribution. A quick, scholarly reaction. I hope that Mr. Ben-Gan appreciated the response, and that this community knows that sharing knowledge is lofty, but giving credit is vital. Without it, it's theft, not sharing.

    I wonder what Senator Paul would say about this!

  • Order #123 ships with N deliveries #ABC on Dec. 2, #DEF on Dec. 3, ..., #XYZ on Dec. 5.

    The table Orders contains a record with

    {

    orderid: #123,

    ...

    }

    and the table Deliveries contains N records with

    {

    deliveryid: #ABC

    deliverydate: 20131202,

    orderid: #123,

    ...

    },

    {

    deliveryid: #DEF

    deliverydate: 20131203,

    orderid: #123,

    ...

    },

    ...,

    {

    deliveryid: #XYZ

    deliverydate: 20131205,

    orderid: #123,

    ...

    }

    Retrieving the date and id (and/or any other value) of the latest delivery regarding order #123 (and any other order with one or more deliveries) is as easy as a:

    WITH OrderDeliveries (

    orderid,

    deliveryid,

    deliverydate,

    rn

    ) AS (

    SELECT

    orderid,

    deliveryid,

    deliverydate,

    ROW_NUMBER() OVER (PARTITION BY orderid ORDER by deliverydate DESC, deliveryid DESC) AS rn

    )

    SELECT

    orderid,

    deliveryid,

    deliverydate

    FROM OrderDeliveries

    WHERE rn = 1

Viewing 15 posts - 31 through 45 (of 287 total)

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