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. Id order 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 won't work in Oracle, as Oracle doesn't have TOP and wildcards have to have the table specified if there are additional columns. – Allan Jan 18 at 22:11.

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 xid create 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).

This should do it The 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 X INNER 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. ID INNER JOIN TABLE_Y Y ON Y.

Y_ID = MAXY. MAXY_ID.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.