Max value in a many-to-many relationship

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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