select a,b,max(c) as c ,identity(int,1,1) as id 
into #t from bd_info
group by b ,a

select * from #t t1
where id  = (
    select top 1 t2.id from #t t2
    where t2.c=(
        select max(t3.c) from #t t3
        where t2.a=t3.a
        ) and t1.a=t2.a
)

------解决方案--------------------
SQL code
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int,[c] int)
insert [tb]
select 1,2,3 union all
select 1,1,1 union all
select 2,2,1 union all
select 2,1,2 union all
select 3,1,1 union all
select 3,2,3 union all
select 3,1,2 union all
select 3,1,3
 
select *
from tb t
where not exists(select 1 from tb where a=t.a and (c>t.c or c=t.c and b>t.b))

/**
a           b           c
----------- ----------- -----------
1           2           3
2           1           2
3           2           3

(3 行受影响)
**/

------解决方案--------------------
SQL code
--------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-18 09:46:23
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] INT,[c] INT)
INSERT [tb]
SELECT 1,2,3 UNION ALL
SELECT 1,1,1 UNION ALL
SELECT 2,2,1 UNION ALL
SELECT 2,1,2 UNION ALL
SELECT 3,1,1 UNION ALL
SELECT 3,2,3 UNION ALL
SELECT 3,1,2 UNION ALL
SELECT 3,1,3
GO
--SELECT * FROM [tb]

-->SQL查询如下:
SELECT A,B,C FROM (SELECT RN=ROW_NUMBER()OVER(PARTITION BY A ORDER BY C DESC,B DESC),* FROM TB) T WHERE RN=1\/*
a           b           c
----------- ----------- -----------
1           2           3
2           1           2
3           2           3

(3 行受影响)
*/

------解决方案--------------------
SQL code
declare @temp_table table(a int,b int,c int)
insert into @temp_table
select a,b,max(c) as c from bd_info
group by b,a
select * from @temp_table t
where not exists(select 1 from @temp_table where a=t.a and (c=t.c and b>t.b or c>t.c))