CODEWITHSUNDEEP
sqlsql-server-2005
JoSav
JoSav

Reputation: 247

Sql get latest records of the month for each name

This question is probably answered before but i cant find how to get the latest records of the months.

The problem is that I have a table with sometimes 2 row for the same month. I cant use the aggregate function(I guess) cause in the 2 rows, i have different data where i need to get the latest.

Example:

name        Date   nuA  nuB nuC     nuD
test1   05/06/2013  356 654 3957    7033
test1   05/26/2013  113 237 399     853
test3   06/06/2013  145 247 68      218
test4   06/22/2013  37  37  6       25
test4   06/27/2013  50  76  20      84
test4   05/15/2013  34  43  34      54

I need to get a result like:

test1   05/26/2013    113    237   399   853
test3   06/06/2013    145    247   68    218
test4   05/15/2013    34     43    34    54
test4   06/27/2013    50     76    20    84

** in my example the data is in order but in my real table the data is not in order.

For now i have something like:

SELECT     Name, max(DATE) , nuA,nuB,nuC,nuD
FROM         tableA INNER JOIN
Group By  Name, nuA,nuB,nuC,nuD

But it didn't work as i want.

Thanks in advance

Edit1:

It seems that i wasn't clear with my question... So i add some data in my example to show you how i need to do it. Thanks guys

Upvotes: 1

Views: 1625

Answers (3)

bvr
bvr

Reputation: 4826

Try this

SELECT t1.* FROM Table1 t1
INNER JOIN 
(
  SELECT [name],MAX([date]) as [date] FROM Table1
            GROUP BY [name],YEAR([date]),MONTH([date])
) t2
ON t1.[date]=t2.[date] and t1.[name]=t2.[name]
ORDER BY t1.[name]

Upvotes: 0

iruvar
iruvar

Reputation: 23374

Use SQL Server ranking functions.

select name, Date, nuA, nuB, nuC, nuD from
(Select *, row_number() over (partition by name, datepart(year, Date),
 datepart(month, Date) order by Date desc) as ranker from Table
) Z
where ranker = 1

Upvotes: 2

Thomas Barnes
Thomas Barnes

Reputation: 76

Can you not just do an order

select * from tablename where Date = (select max(Date) from tablename)

followed by only pulling the first 3?

Upvotes: -1

Related Questions