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.
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