# Max value in a many-to-many relationship

I'm using SQL Server 2008 and I have 3 tables, x, y and z. y exists to create a many-to-many relationship between x and z.

``` x      y      z
--     --     --
id    xid     id
zid   sort
```

All of the above fields are int.

I want to find the best-performing method (excluding denormalising) of finding the z with the highest sort for any x, and return all fields from all three tables.

Sample data:

```x:   id
--
1
2

y:  xid zid
--- ---
1   1
1   2
1   3
2   2

z:  id sort
-- ----
1    5
2   10
3   25
```

Result set should be

```xid zid
--- ---
1   3
2   2
```

Note that if more than one z exists with the same highest sort value, then I still only want one row per x.

Note also that in my real-world situation, there are other fields in all three tables which I will need in my result set.

One method is with a sub query. This however is only good for getting the ID of Z. If you need more/all columns from both x and z tables then this is not the best solution.

```SELECT
x.id,
(
SELECT TOP 1
z.zid
FROM
y
INNER JOIN
z
ON
z.id = y.zid
WHERE
y.xid = x.id
ORDER BY
z.sort DESC
)
FROM
x
```

This is how you can do it and return all the data from all the tables.

```SELECT
*
FROM
x
INNER JOIN
y
ON
y.xid = x.id
AND
y.zid =
(
SELECT TOP 1
z2.zid
FROM
y y2
INNER JOIN
z z2
ON
z2.id = y2.zid
WHERE
y2.xid = x.id
ORDER BY
z2.sort DESC
)
INNER JOIN
z
ON
z.id = y.zid
```

```select xid,max(zid) as zid from y
group by xid
```

```select xid, zid /* columns from x; and columns from y or z taken from q */
from (select y.xid, y.zid, /* columns from y or z */
row_number() over(partition by y.xid order by z.sort desc) r
from y
join z on z.id = y.zid
) q
join x on x.id = q.xid
where r = 1
```