Filter table to show only most recent values

Question

I have a table that looks like this.

Category	Type	fromDate	Value
1	1	1/1/2022	5
1	2	1/1/2022	10
2	1	1/1/2022	7.5
2	2	1/1/2022	15
3	1	1/1/2022	3.5
3	2	1/1/2022	5
3	1	4/1/2022	5
3	2	4/1/2022	10

I'm trying to filter this table down to filter down and keep the most recent grouping of Category/Type. IE rows 5 and 6 would be removed in the query since they are older records.

So far I have the below query but I am getting an aggregate error due to not aggregating the "Value" column. My question is how do I get around this without aggregating? I want to keep the actual value that is in the column.

    SELECT T1.Category, T1.Type, T2.maxDate, T1.Value 
    FROM (SELECT Category, Type, MAX(fromDate) AS maxDate 
            FROM Table GROUP BY Category,Type) T2
    INNER JOIN Table T1 ON T1.Category=T2.Category
    GROUP BY T1.Category, T1.Type, T2.MaxDate

score 0 · Accepted Answer · answered Apr 07 '22 at 16:13

This has been asked and answered dozens and dozens of times. But it was quick and painless to type up an answer. This should work for you.

declare @MyTable table
(
    Category int
    , Type int
    , fromDate date
    , Value decimal(5,2)
)

insert @MyTable
select 1, 1, '1/1/2022', 5 union all
select 1, 2, '1/1/2022', 10 union all
select 2, 1, '1/1/2022', 7.5 union all
select 2, 2, '1/1/2022', 15 union all
select 3, 1, '1/1/2022', 3.5 union all
select 3, 2, '1/1/2022', 5 union all
select 3, 1, '4/1/2022', 5 union all
select 3, 2, '4/1/2022', 10

select Category
    , Type
    , fromDate
    , Value
from
(
    select *
        , RowNum = ROW_NUMBER() over(partition by Category, Type order by fromDate desc)
    from @MyTable
) x
where x.RowNum = 1
order by x.Category
    , x.Type

Filter table to show only most recent values

1 Answers1