SQLServerCentral apologizes and you can win a book

  • WITH CTE

    AS

    (

    SELECT t.x

    FROM

    ( VALUES

    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)

    )t (x)

    ),

    CTE2

    AS

    (

    SELECT ROW_NUMBER() OVER(ORDER BY c.x) AS RowNum

    FROM CTE c

    CROSS JOIN CTE c2

    CROSS JOIN CTE c3

    CROSS JOIN CTE c4

    CROSS JOIN CTE c5

    ),

    CTE3

    AS

    (

    SELECT CAST(c.RowNum AS NUMERIC(15,2)) AS ProductCost,

    c.RowNum%5 AS ProductID

    FROM CTE2 c

    )

    SELECT DISTINCT c.ProductID,

    SUM(c.ProductCost) OVER(PARTITION BY c.ProductID ORDER BY c.ProductID) AS TotalProductCost

    FROM CTE3 c

    ORDER BY c.ProductID

    ;

  • How does this look...simple

    Use MYDb;

    GO

    SELECT ROW_NUMBER() OVER(ORDER BY X) AS Row,

    A, B, C

    FROM Z;

  • I applaud your steps to respect Ben-Gan's intellectual property. He is certainly the go-to resource for depth of knowledge on all sorts of querying topics.

    Here is a bit of actual code I wrote as part of a solution to list families and include the date of birth for the oldest child, which was important for the specific reporting requirements of the complete solution. What is shown below was used as a CTE in the complete query solution.

    select

    families.family_id,

    children.person_id,

    children.nick_name,

    children.last_name,

    children.birth_date,

    children.gender,

    oldest_child_birth_date = min(children.birth_date) over (partition by families.family_id)

    from

    dbo.core_person as children inner join

    dbo.core_family_member as families on children.person_id = families.person_id inner join

    dbo.core_lookup as family_roles on families.role_luid = family_roles.lookup_id

    where

    family_roles.lookup_value = 'Child'

  • Okay, here's my ticket.

    Once upon a time there was a song Bridge over troubled water.. by Simon & Garfunkel!

    select row_number() over (order by bridge_id) as troubled_water_id, river

    from rivers

    Best of all to SQL community

    Grega Jesih

    PS

    I read this article especially because as soon as I spotted a legend name of Mr BenGan, it caught my attention.

  • Good form.

    Hopefully this will put a smile on Jeff's face (I'm using your TallyTable!) I love that by the way, so handy.

    I used something similar to this recently...it's strange and was for a very specific purpose but it does have window functions in it. 🙂

    DECLARE @Start datetime = DATEADD(minute,5,GETDATE())

    ,@End datetime = DATEADD(minute,12,GETDATE());

    ;WITH SomeData AS (

    SELECT TOP 25 100 AS [RowId]

    ,'Something: '+CHAR(N+47) AS [Something]

    ,DATEADD(minute,N-1,GETDATE()) AS [SomeDate]

    FROM dbo.TallyTable

    UNION ALL

    SELECT TOP 6 200 AS [RowId]

    ,'Something: '+CHAR(N+47) AS [Something]

    ,DATEADD(minute,N,GETDATE()) AS [SomeDate]

    FROM dbo.TallyTable

    UNION ALL

    SELECT TOP 35 300 AS [RowId]

    ,'Something: '+CHAR(N+47) AS [Something]

    ,DATEADD(minute,N-2,GETDATE()) AS [SomeDate]

    FROM dbo.TallyTable

    )

    SELECT RowId

    ,Something

    ,SomeDate

    ,@Start AS [StartDate]

    ,@End AS [EndDate]

    ,CASE WHEN SomeDate > @Start AND SomeDate < @End THEN 'Between @Start And @End'

    WHEN SomeDate <= @Start THEN 'Less Than @Start'

    ELSE 'Greater Than @End'

    END AS [TimeState]

    ,ROW_NUMBER() OVER(PARTITION BY RowId ORDER BY SomeDate DESC) AS [RowNum]

    ,ROW_NUMBER() OVER(PARTITION BY RowId

    ,CASE WHEN SomeDate > @Start AND SomeDate < @End THEN 2

    WHEN SomeDate <= @Start THEN 3

    ELSE 1

    END

    ORDER BY SomeDate DESC) AS [LessThan_@Start_Records]

    ,ROW_NUMBER() OVER(PARTITION BY RowId

    ,CASE WHEN SomeDate > @Start AND SomeDate < @End THEN 2

    WHEN SomeDate <= @Start THEN 3

    ELSE 1

    END

    ORDER BY SomeDate ASC) AS [GreaterThan_@End_Records]

    FROM SomeData;

  • select row_number() over (order by type)

    from sys.objects

  • use AdventureWorks

    SELECT SalesOrderID, ProductID, OrderQty,

    ROW_NUMBER() OVER ( PARTITION BY SalesOrderID ORDER BY ProductID ) AS GNum,

    ROW_NUMBER() OVER ( ORDER BY ProductID ) AS CNum

    FROM Sales.SalesOrderDetail

  • Thats a nice gesture from you to issue a public apology and give away Bem-Gan's books.

    Here is a production SQL frm my DB to get latest payment details by account num.

    select * from (

    select rank() over (partition by ACCOUNT_NUM order by PAYMENT_DATE desc) as Rnk, ACCOUNT_NUM, PAYMENT_DATE, PAYMENT_AMOUNT

    from FACT_ACCOUNTT_PAYMENTT

    where PAYMENT_DATE between '2013-10-01' and '2013-11-30'

    ) as x where Rnk =1

    order by ACCOUNT_NUM, PAYMENT_DATE asc

  • It happens sometimes and its indeed great for apology.

    Simple example..

    SELECT SCHEMA_NAME(schema_id) ,

    name ,

    ROW_NUMBER() OVER ( PARTITION BY SCHEMA_NAME(schema_id) ORDER BY SCHEMA_NAME(schema_id), name ) AS TableIndex

    FROM sys.tables

  • I use window functions all the time! Would love to know how to enhance them even further.

    SELECT t.Name,

    t.CountryRegionCode,

    t.[Group],

    t.SalesYTD,

    RANK() OVER (PARTITION BY [Group] ORDER BY SalesYTD) AS SubRank,

    RANK() OVER (ORDER BY SalesYTD) AS OverallRank

    FROM sales.SalesTerritory t

    😀

  • Fragments in full-text catalog

    select object_name([table_id]) as TableName

    , count([fragment_id]) over(partition by table_id) as Fragments

    from sys.fulltext_index_fragments

  • My opinion: MSSQL window function are a great way to manipulate data, promoting the set based approach

    --An example of how to simply remove duplicates from the data table using window function

    --we want to delete duplicates and keep the records that were last added (in accordance with the desired key)

    create table #names(first_name nvarchar(20), last_name nvarchar(50), record_added datetime)

    insert into #names (first_name, last_name, record_added )

    select

    'John', 'Doe', getdate() union all

    select

    'John', 'Doe', dateadd(hh,-1,getdate()) union all

    select

    'John', 'Doe', dateadd(hh,-2,getdate()) union all

    select

    'Jane', 'Doe', getdate() union all

    select

    'Jane', 'Doe', dateadd(hh,-1,getdate()) union all

    select

    'Jane', 'Doe', dateadd(hh,-2,getdate()) union all

    select

    'Some', 'Other Guy', null

    select * from #names

    begin try

    create unique index main on #names (first_name, last_name) --no can do for there are duplicate first_name+last_name values

    end try

    begin catch

    ;with duplicates as

    (select

    row_number() over (partition by first_name, last_name order by first_name, last_name, record_added desc) rn

    from

    #names)

    delete from

    duplicates

    where

    rn > 1

    create unique index main on #names (first_name, last_name) --Works just fine, for there are no duplicates left

    end catch

    select * from #names

    drop table #names

  • Nice! 😀

    Query to show lowest date for a key where date as more than five entries

    ;WITH Test (SomeKey,SomeDate,RowNo)

    AS (

    SELECT SomeKey,SomeDate

    ,ROW_NUMBER() OVER (PARTITION BY SomeKey ORDER BY SomeDate ASC)

    FROM SomeTable

    GROUP BY SomeKey,SomeDate

    HAVING COUNT(*) > 5

    )

    SELECT SomeKey,SomeDate

    FROM Test

    WHERE RowNo = 1

    Far away is close at hand in the images of elsewhere.
    Anon.

  • It's a shame this guy's work was plagiarized.

    I have been enjoying the window functions!

    ;WITH

    cteT1 AS

    (

    SELECT 1 i UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ),

    rnT2 AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 i FROM cteT1 a, cteT1 b

    ),

    rnT3 AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 i FROM cteT1 a, cteT1 b, cteT1 c

    )

    SELECT

    i

    ,ROW_NUMBER() OVER (PARTITION BY i/5 ORDER BY i) iByFive

    FROM rnT3

    ORDER BY i

  • CREATE TABLE #nn0(

    iden [int] NOT NULL,

    seqnum [int] NOT NULL,

    thisYR [int] NOT NULL,

    result [int] NULL,

    CONSTRAINT PK_0 PRIMARY KEY CLUSTERED (iden ASC,seqnum ASC))

    INSERT INTO #nn0 (iden, seqnum, thisYR, result)

    VALUES

    (1,1,2012,2), (1,2,2012,4), (1,3,2013,3), (1,4,2013,2), (1,5,2013,4),

    (2,1,2014,1), (3,1,2014,3), (4,1,2014,5)

    SELECT * FROM #nn0

    SELECT iden, thisYR, result

    FROM (SELECT iden, seqnum, thisYR, result

    , ROW_NUMBER() OVER (PARTITION BY iden ORDER BY iden, thisYR DESC) AS rk

    FROM #nn0) x

    WHERE rk = 1

    DROP TABLE #nn0

    --Easy

Viewing 15 posts - 211 through 225 (of 288 total)

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