views:

1490

answers:

4

Hi everyone.

I have the following table in MSSQL2005

id | business_key | result
1 | 1 | 0
2 | 1 | 1
3 | 2 | 1
4 | 3 | 1
5 | 4 | 1
6 | 4 | 0

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

business_key | result
1 | 1
2 | 1
3 | 1
4 | 0

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

+2  A: 
select
  drv.business_key,
  mytable.result
from mytable
  inner join
  (
    select 
      business_key, 
      max(id) as max_id
    from mytable
    group by
      business_key
  ) as drv on
    mytable.id = drv.max_id

I think

Chris Simpson
+1  A: 

Try this

select  business_key, 
     result
from    myTable
where   id in 
     (select max(id)
     from myTable
     group by business_key)

EDIT: I created the table to test my code. I include it below in case anybody else wants to test it.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[myTable](
    [id] [int] NOT NULL,
    [business_key] [int] NOT NULL,
    [result] [int] NOT NULL
) ON [PRIMARY]
go

insert into myTable values(1,1,0);
insert into myTable values(2,1,1);
insert into myTable values(3,2,1);
insert into myTable values(4,3,1);
insert into myTable values(5,4,1);
insert into myTable values(6,4,0);

select  * from mytable
John MacIntyre
That query will only show one row of data, not four. You need to make the sub-query correlated - and you need aliases for the two instances of MyTable (call them 'first' and 'second'); add WHERE first.id = second.id
Jonathan Leffler
Jonathan-You are correct, there was a typo in the where clause. It should be 'in' not '='. But the alias's are not necessary. Thx for pointing out my error.
John MacIntyre
+3  A: 

An alternative solution, which may give you better performance (test both ways and check the execution plans):

SELECT
     T1.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

This query assumes that the ID is a unique value (at least for any given business_key) and that it is set to NOT NULL.

Tom H.
wow, you are right. Query costs 44% instead of 56%. Thanks a lot!
computhomas
A: 
select business_key, 
       result
    from 
    (select id, 
        business_key, 
        result, 
        max(id) over (partition by business_key) as max_id
    from mytable) x
where id = max_id