Need one row only returned from INNER JOIN

Try this query

select *,(select top 1 creationdate from Table_Y where from Table_Y.xId = m.idorder by Table_Y.CreationDate ) from Table_X m

The sub query will pick the top 1 result which have max creationdate and the main query will pick all the records so you have your desired result

This should do itThe complex subquery works out the max date for each Y.xid group, and from that, further works out the Max Y_ID (let this represent the key on table Y)

SELECT X.*, Y.*FROM TABLE_X XINNER JOIN (    SELECT t1.xid, Max(t1.Y_id) MaxY_id    FROM       (SELECT t2.xid, MAX(t2.CREATIONDATE) MDate        FROM TABLE_Y t2        GROUP BY t2.xid) t    inner join TABLE_Y t1        on t.xid=t1.xid and t.MDate = t1.CREATIONDATE) MAXY    ON MAXY.xid = X.IDINNER JOIN TABLE_Y Y    ON Y.Y_ID = MAXY.MAXY_ID

"when I add it to an inner join"? what inner join? with what inner join? The question is badly underspecified, but I think you need this (I only use views to be clear, you can easily just put them in braces and build one big query):

-- latest pairs of (id,creation) per xidcreate view latest_id_per_xid as   select xid,max(id) id,max(creation) creation from table_y group by xid;-- this view leaves only the rows from table_y that have the same (id,creation,idx)-- as the newest rows identified in the former view (it's basically a semijoin)-- you could also join on id alone create view latest_whole_y as   select table_y.* from table_y natural join latest_id_per_xid;-- now the answer is easy:select * from table_x join latest_whole_y

I have no database at hand to check for small mistakes, but it should run fine.(caveat: the big assumption is that you never have a record with a newer id and older date)