SQL分组返回表的所有列

SQL code

declare  @table1 table (id int,price int,starttime datetime, orderid int)insert into @table1select 1,50,'2012-5-1',1union select  2,100,'2012-5-1',1union select  3,50,'2012-5-1',2union select  4,60,'2012-5-1',2union select  5,70,'2012-5-1',2union select  6,70,'2012-5-1',3union select  7,90,'2012-5-1',3 ;witha as (   select orderid,max(price) as maxprice from @table1 group by orderid)select [@table1].* from @table1,a where [@table1].orderid = a.orderid and [@table1].price = a.maxprice

我想根据@table1中的orderid进行分组，返回最大出价(price）的记录的所有列，以上是我写的sql代码示例，感觉写的还是不够好，大虾们有什么更加好的方法吗？

[解决办法]

SQL code

declare  @table1 table (id int,price int,starttime datetime, orderid int)insert into @table1select 1,50,'2012-5-1',1union select  2,100,'2012-5-1',1union select  3,50,'2012-5-1',2union select  4,60,'2012-5-1',2union select  5,70,'2012-5-1',2union select  6,70,'2012-5-1',3union select  7,90,'2012-5-1',3 union select  8,90,'2012-5-1',3 ;--1SELECT * FROM @table1 a WHERE NOT EXISTS(SELECT 1 FROM @table1 b WHERE a.orderid=b.orderid AND a.price<b.price)--SELECT * FROM @table1 a WHERE NOT EXISTS(SELECT 1 FROM @table1 b WHERE a.orderid=b.orderid AND a.price<b.price OR (a.orderid=b.orderid  AND a.id<b.id))--2SELECT * FROM @table1 a WHERE 1>(SELECT COUNT(*) FROM @table1 b WHERE a.orderid=b.orderid AND a.price<b.price)--3;WITH t AS(SELECT *,row=ROW_NUMBER()OVER(PARTITION BY orderid ORDER BY price DESC) FROM @table1)SELECT * FROM t WHERE row=1