MSSQL: Only last entry in GROUP BY (with id)

Question

Hi everyone. Following / copying computhomas's question, but adding some twists...

I have the following table in MSSQL2008

id | business_key | result | date
1 | 1 | 0 | 9
2 | 1 | 1 | 8
3 | 2 | 1 | 7
4 | 3 | n | 6
5 | 4 | 1 | 5
6 | 4 | 0 | 4

And now i want to group based on the business_key returning the complete entry with the newest date. So my expected result is:

id | business_key | result | date
1 | 1 | 0 | 9
3 | 2 | 1 | 7
4 | 3 | n | 6
5 | 4 | 1 | 5

I also bet that there is a way to achieve that, i just can't find / see / think of it at the moment.

edit: sorry about this, I actually meant something else from original question I did. I felt like editing this might be better than accepting a solution and making another question. my original problem was that I am not filtering by id.

Answer 1

How about (edited after question change):

with latestdate as (
  select business_key, maxdate=max(date)
  from the_table
  group by business_key
), latest as (
  select ID = max(id)
  from the_table
    inner join latestdate
     on the_table.business_key=latestdate.business_key
       and the_table.date=latestdate.maxdate
  group by the_table.business_key
)
select the_table.*
from the_table
  inner join latest
    on latest.id=the_table.id

Answer 2

SELECT 
       *
FROM 
       mytable 
WHERE 
       ID IN (SELECT MAX(ID) FROM mytable GROUP BY business_key)

Answer 3

SELECT t.*
FROM
(
    SELECT *, ROW_NUMBER() OVER
              (
                  PARTITION BY [business_key]
                  ORDER BY [date] DESC
              ) AS [RowNum]
    FROM yourTable
) AS t
WHERE t.[RowNum] = 1

Answer 4

SELECT 
     MAX(T1.id) AS [id],
     T1.business_key, 
     T1.result 
FROM 
     dbo.My_Table T1 
LEFT OUTER JOIN dbo.My_Table T2 ON 
     T2.business_key = T1.business_key AND 
     T2.id > T1.id 
WHERE 
     T2.id IS NULL 
GROUP BY T1.business_key, 
     T1.result 
ORDER BY MAX(T1.id)

Edited based on clarifications

SELECT M1.*
FROM My_Table M1
INNER JOIN 
(
    SELECT [business_key], MAX([date]) as MaxDate
    FROM My_Table 
    GROUP BY [business_key]
) M2 ON M1.business_key = M2.business_key AND M1.[date] = M2.MaxDate
ORDER BY M1.[id]

Answer 5

Assuming the combination of business_key & date is unique then....

Working example (3rd time is a charm):

declare @src as table(id int, business_key int,result int,[date] int)
insert into @src
SELECT 1,1,0,9
UNION SELECT 2,1,1,8
UNION SELECT 3,2,1,7
UNION SELECT 4,3,1,6
UNION SELECT 5,4,1,5
UNION SELECT 6,4,0,4

;with bkdate(business_key,[date])
AS
(
    select business_key,MAX([date])
    from @src 
    group by business_key
)
select src.* from @src src 
inner join bkdate
ON src.[date] = bkdate.date
and src.business_key = bkdate.business_key
order by id