CODEWITHSUNDEEP
sql-serversql-server-2016partitioning
tomdemaine
tomdemaine

Reputation: 758

Partition by syntax

I have the following statement which works to get the most recent row of data for a particular DDI. What I now want to do is replace the single DDI in the where statement with a long list of them but still have only the most recent row for each. I'm pretty sure that I need to use OVER and PARTITION BY to get a separate window for each DDI but even reading the microsoft documentation and a more simplified tutorial I still can't get the syntax right. I suspect I just need a nudge in the right direction. Can anyone help?

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

http://www.sqltutorial.org/sql-window-functions/sql-partition-by/

SELECT TOP 1
       [Start Time]
      ,[Agent Name]
      ,[Reference]
      ,[charged op. (sec)]
      ,[Type]         
      ,[Activation ID] as [actid]          
  FROM [iPR].[dbo].[InboundCallsView]    
  Where [type] = 'Normal operator call'    
  AND [DDI] = @DDI    
  Order By [Start Time] Desc

Upvotes: 0

Views: 254

Answers (2)

Zack
Zack

Reputation: 2341

So let's assume a table with this data (notice how I cleaned up the column names to remove spaces, special characters, etc.):

+---+------------------+--------+------+----+------+---+
| 1 | 2019-03-28 08:00 | agent1 | foo1 | 60 | foo1 | 1 |
+---+------------------+--------+------+----+------+---+
| 1 | 2019-03-28 09:00 | agent2 | foo2 | 70 | foo2 | 2 |
| 2 | 2019-03-27 08:00 | agent3 | foo3 | 80 | foo3 | 3 |
| 2 | 2019-03-27 09:00 | agent4 | foo4 | 90 | foo4 | 4 |
+---+------------------+--------+------+----+------+---+

As you say, you can use a window function to get what you want. However, let me show you a method that doesn't require a window function first.

You want records where the StartTime is the max value for that DDI. You can obtain the max StartTime for each DDI with the following query:

SELECT
    ddi, 
    max_start = MAX(StartTime)
FROM InboundCallsView
GROUP BY ddi

You can then join that query to your base table/view to get the records you want. Using an intermediate CTE, you can do the following:

WITH 
    ddiWithMaxStart AS
    (
        SELECT
            ddi, 
            max_start = MAX(StartTime)
        FROM InboundCallsView
        GROUP BY ddi
    )
SELECT InboundCallsView.*
FROM InboundCallsView
    INNER JOIN ddiWithMaxStart ON
        ddiWithMaxStart.ddi = InboundCallsView.ddi
        AND ddiWithMaxStart.max_start = InboundCallsView.StartTime

Now, if you really want to use WINDOW functions, you can use ROW_NUMBER for a similar effect:

WITH 
    ddiWithRowNumber AS
    (
        SELECT
            InboundCallsView.*, 
            rn = ROW_NUMBER() OVER
            (
                PARTITION BY ddi
                ORDER BY ddi, StartTime DESC
            )
        FROM InboundCallsView
    )
SELECT *
FROM ddiWithRowNumber
WHERE rn = 1

Notice that with this method, you don't need to join the base view/table to the intermediate CTE.

You can test out performance of each method to see which works best for you.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

Not sure how you plan on handling the multiple values for DDI but that may be an issue. The best approach would be to use a table valued parameter. If you pass in a delimited list you have to split the string too which is not a good way of handling this type of thing.

This query will return the most recent for every DDI. 

SELECT 
    [Start Time]
    , [Agent Name]
    , [Reference]
    , [charged op. (sec)]
    , [Type]
    , [actid]
from
(
    SELECT 
        [Start Time]
        , [Agent Name]
        , [Reference]
        , [charged op. (sec)]
        , [Type]
        , [actid]
        , RowNum = ROW_NUMBER() over(partition by DDI order by [Start Time] desc)
      FROM [iPR].[dbo].[InboundCallsView]

      where [type] = 'Normal operator call'
        --and [DDI] = @DDI
) x
where x.RowNum = 1

Upvotes: 2

Related Questions