Maximum many-to-many relationship

Question

I am using SQL Server 2008, and I have 3 tables x, yand z. yexists to create a many-to-many relationship between xand z.

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

All of the above fields int.

I want to find the most efficient method (excluding denormalization) for finding zthe highest sortfor any xand 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

The result set should be

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

Please note that if there is more than one zwith the same highest value sort, then I have only one line left for x.

, , .

Answer 1

- . Z. / x z, .

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

.

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 2

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

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