sql - Limit a sorted number of rows joined -

i have 2 tables, , b, , join table m. want to, each a.id, top 2 b.id's sorting on value in table m, producing results below. running on azure sql database

table          table m          table b +-----+   +-----+-----+-------+   +-----+ |  id |   | aid | bid | value |   |  id | +-----+   +-----+-----+-------+   +-----+ |   1 |   |   1 |   3 |     4 |   |   1 | |   2 |   |   1 |   2 |     3 |   |   2 | |   3 |   |   3 |   2 |     3 |   |   3 | |   4 |   |   3 |   5 |     6 |   |   4 | +-----+   |   3 |   3 |     4 |   |   5 |           |   4 |   1 |     2 |   +-----+           |   4 |   2 |     1 |           |   4 |   4 |     3 |           +-----+-----+-------+                   result           +-----+-----+-------+           | aid | bid | value |           +-----+-----+-------+           |   1 |   3 |     4 |           |   1 |   2 |     3 |           |   3 |   5 |     6 |           |   3 |   3 |     4 |           |   4 |   1 |     2 |           |   4 |   4 |     3 |           +-----+-----+-------+ 

i know can select m.aid rows equal 1, sort it, , limit 2, need every row in table a. i've made attempt use group by, wasn't sure how sort , limit it. i've tried search resources associated issue couldn't find resources.

(i wasn't sure how word title issue)

you can use row_number:

select     aid, bid, value (     select *,         rn = row_number() over(partition aid order value desc)     m ) t rn <= 2