CODEWITHSUNDEEP
sqlsql-serverwindow-functions
user3290807
user3290807

Reputation: 391

Partitioning data in SQL Server

I have the following data set and i am trying to use a row rank logic in order to get the output mentioned below:

declare  @data table
       ( 
          identifier int,
          value float,
          dateValue datetime
        )
insert into @data values( 1 , 100 ,'2016-08-09 11:00:00.000')
insert into @data values( 1 , 150 ,'2016-08-09 11:00:00.000')
insert into @data values( 1 , 200 ,'2014-08-09 11:00:00.000')
insert into @data values( 2 , 400 ,'2016-08-09 11:00:00.000')
insert into @data values( 2 , 300 ,'2012-08-09 11:00:00.000')

the output i was expecting was : to pick up the first value for Value column based on the latest date for a given id and the aggregation for a given id.

id  Value  AggValue         Date
1   100      450           2016-08-09 11:00:00.000
2   400      700           2016-08-09 11:00:00.000

I tried using the following query to get similar output but i couldn't figure out how to calculate the Aggvalue in the same query

SELECT identifier,value,dateValue FROM
      (SELECT  identifier,value,dateValue,
      ROW_NUMBER() OVER (PARTITION BY t.identifier ORDER BY t.dateValue DESC) AS [Rank]
      FROM @data t) AS [sub]
      where [sub].[Rank] = 1

Upvotes: 1

Views: 75

Answers (2)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

You just miss to SUM(value):

SELECT 
    identifier,
    value,
    dateValue 
FROM
(
    SELECT  
        identifier,
        dateValue,
        SUM(VALUE) OVER(PARTITION BY t.identifier ORDER BY t.identifier) AS Value,
        ROW_NUMBER() OVER (PARTITION BY t.identifier ORDER BY t.dateValue DESC) AS [Rank]
    FROM @data t
) AS [sub]
where [sub].[Rank] = 1

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

Use SUM() OVER:

SELECT TOP 1 WITH TIES identifier id,value,SUM(value) OVER (PARTITION BY identifier) AggValue,dateValue
FROM @data
ORDER BY ROW_NUMBER() OVER (PARTITION BY identifier ORDER BY datevalue DESC)

Result:

id          value                  AggValue               dateValue
----------- ---------------------- ---------------------- -----------------------
1           100                    450                    2016-08-09 11:00:00.000
2           400                    700                    2016-08-09 11:00:00.000

Upvotes: 4

Related Questions