sql - How to combine GROUP BY and ROW_NUMBER? -

i hope following sample code self-explanatory:

declare @t1 table (id int,price money, name varchar(10)) declare @t2 table (id int,orders int,  name varchar(10)) declare @relation  table (t1id int,t2id int) insert @t1 values(1, 200, 'aaa'); insert @t1 values(2, 150, 'bbb'); insert @t1 values(3, 100, 'ccc'); insert @t2 values(1,25,'aaa'); insert @t2 values(2,35,'bbb'); insert @relation values(1,1); insert @relation values(2,1); insert @relation values(3,2);  select t2.id t2id ,t2.name t2name ,t2.orders ,t1.id t1id ,t1.name t1name ,t1sum.price @t2 t2 inner join (     select rel.t2id         ,max(rel.t1id)as t1id  -- max returns arbitrary id, need is:  --      ,row_number()over(partition rel.t2id order price desc)as pricelist         ,sum(price)as price         @t1 t1          inner join @relation rel on rel.t1id=t1.id         group rel.t2id )as t1sum on  t1sum.t2id = t2.id inner join @t1 t1 on t1sum.t1id=t1.id 

result:

t2id   t2name   orders  t1id    t1name  price       1      aaa       25     2       bbb    350,00       2      bbb       35     3       ccc    100,00 

what need commented above, way row_number group by in first place. need sum of t1-prices grouped t2.id in relation-table , in outer query t1id highest price.

in other words: how change max(rel.t1id)as t1id returning id highest price?

so desired result is(notice first t1id changed 2 1 since has higher price):

t2id   t2name   orders  t1id    t1name  price       1      aaa       25     1       aaa    350,00       2      bbb       35     3       ccc    100,00 

note: in case you're wondering why don't multiply orders price: not realated(so should have left off column since it's bit ambiguous, please ignore it, i've added make less abstract). orders must remain unchanged, that's reason sub-query approach join both , reason why need group in first place.

conclusion: core of question can answered over clause can applied any aggregate function sum(see damien's answer) new me. thank working approaches.

wow, other answers complex - i'm hoping i've not missed obvious.

you can use over/partition by against aggregates, , they'll grouping/aggregating without group by clause. modified query to:

select t2.id t2id ,t2.name t2name ,t2.orders ,t1.id t1id ,t1.name t1name ,t1sum.price @t2 t2 inner join (     select rel.t2id         ,rel.t1id  --       ,max(rel.t1id)as t1id  -- max returns arbitrary id, need is:        ,row_number()over(partition rel.t2id order price desc)as pricelist         ,sum(price)over(partition rel.t2id) price         @t1 t1          inner join @relation rel on rel.t1id=t1.id --        group rel.t2id )as t1sum on  t1sum.t2id = t2.id inner join @t1 t1 on t1sum.t1id=t1.id t1sum.pricelist = 1 

which gives requested result set.