How to get the latest entry for each item for in a Month with a single SQL query

Question

I am trying to write a query to pick one entry for each item for each month but the latest in the month from the following table:

Name  | Date      | Value
a     |2015-01-01 |   1
a     |2015-01-02 |   2
b     |2015-01-03 |   1
b     |2015-01-04 |   1
b     |2015-01-03 |   3
c     |2015-01-02 |   2
c     |2015-01-29 |   10
a     |2015-02-10 |   2
a     |2015-02-20 |   1
c     |2015-02-10 |   2
c     |2015-02-22 |   23
b     |2015-02-25 |   1
b     |2015-02-19 |   2

return should be:

 a     |2015-01-02 |   2
 b     |2015-01-04 |   1
 c     |2015-01-29 |   10
 a     |2015-02-20 |   1
 b     |2015-02-25 |   1
 c     |2015-02-22 |   23

I wonder how would this be achieved instead of sending multiple queries to SQL server for each month I would like to load all the values with one query then filter the collection on the memory. Otherwise I would end up writing a query as below:

SELECT Name,Date, Value FROM MyTable mt
INNER JOIN (
select max(Date) as MaxDate
    FROM [MyTable] m WHERE YEAR(Date) =YEAR(@date) 
     AND MONTH(Date)=MONTH(@date)) mx ON t.Date = mx.MaxDate)

And this query needs to be run for each month.

Any better idea to return all entries with a single query?

Thanks,

Answer 1

Try grouping by year and month in the derived table:

SELECT t1.Name, t1.[Date], t1.Value 
FROM MyTable t1
INNER JOIN (
   SELECT Name, YEAR(Date) AS y, MONTH([Date]) AS m, MAX([Date]) as MaxDate
   FROM MyTable  
   GROUP BY Name, YEAR(Date), MONTH([Date])
) t2 ON t1.Name = t2.Name AND
        YEAR(t1.[Date]) = t2.y AND MONTH(t1.[Date]) = t2.m AND 
        t1.[Date] = t2.MaxDate

Answer 2

 SELECT *
 FROM (
      SELECT NAME, DATE, VALUE,
             ROW_NUMBER() OVER (PARTITION BY NAME, YEAR(Date), MONTH(Date) 
                                ORDER BY Date DESC) rn
      FROM MyTable) AS t
 WHERE t.rn = 1

Answer 3

Assuming that you are using a SQL Server version that supports it, you can use the ROW_NUMBER() windowing function to return a sequence number for each row, then you can subsequently use that to restrict to only the rows that you require.

SELECT [Name],[Date],[Value]
  ,ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Date] DESC) AS [Seq]
FROM myTable

Things to consider:

• What happens when there is a tie? ROW_NUMBER will always return a sequence number, but if your data has > 1 row at the same Date value, the order will be arbritrary. To solve this add additional tie-break ORDER BY entries
  • How do I filter this? Put it into a Common Table Expression, Inline View or Real View

Answer 4

Use Cross apply

SELECT b.* 
FROM   mytable mt 
CROSS apply (SELECT TOP 1 NAME, date, value 
             FROM   [mytable] m 
             WHERE  m.NAME = mt.NAME 
               AND  Month(m.date) = Month(mt.date) 
               AND  Year(m.date) = Year(mt.date) 
             ORDER  BY m.date DESC) b 

Answer 5

I think you need a correlated query once you have a set of distinct (Name, Month). There are various ways of doing this, one is to use cross apply:

 select *
 from (select distinct Name, Month(Date) as Month
       from theTable) itemMonths
   cross apply (select Max(value)
                from theTable t
                where Month(t.Date) = itemMonths.Month
                     and t.Name = itemMonths.Name)

Answer 6

You could try the following:

WITH MyTable AS (SELECT 'a' AS name, GETDATE() AS date, 1 AS value UNION ALL SELECT 'a', GETDATE()+1, 2 ) , res AS ( SELECT Name,date,MAX(Date) OVER(PARTITION BY Name, DATEPART(yyyy,date), DATEPART(mm, date)) AS max_date , Value FROM MyTable ) SELECT name,date,res.value FROM res WHERE date=max_date

You still need a filter though as the Max Over will return all rows.

If you were using Teradata I'd suggest using the Qualify Clause but Itzik hasn't had any luck getting this ported to SQL server! https://connect.microsoft.com/SQLServer/feedback/details/532474