Search code examples
sql-serversummarize

SQL Server: summarize results using max of other column

I'm struggling with this one. I have the following data in the table (actually much more columns to be reduced to):

Data example!

The question is how do I get to the result?

The rules are that I only want just one row per subproduct taking only the Max(changed).

Can you help me?

I tried Group by Product, subproduct but I failed miserably

HELP!

Solution

  • Consider the following (change Table1 to your table name):

    select a.*
from Table1 a inner join
(
    select b.product, b.subproduct, max(b.changed) as mc
    from Table1 b
    group by b.product, b.subproduct
) c on 
a.product = c.product and 
a.subproduct = c.subproduct and 
a.changed = c.mc

    The subquery selects the Product and Subproduct with the latest Changed value and the outermost query joins the original table with the record selected by the subquery.