Need one row only returned from INNER JOIN

I would like to return the first row only from an inner join. I have two tables:

TABLE_X |  TABLE_Y
id      |  id   creationdate  xid 
1       |  1    01/01/2011    1
2       |  2    01/01/2011    1
3       |  3    31/12/2010    2
4       |  4    28/12/2010    3

Rows in TABLE Y can have identical creation dates so I am first getting the MAX(creationdate) and then then MAX(id) from this set, for example:

SELECT  a.id,
        c.id,
        d.id,
        e.id,
        d.CREATIONDATE,
        a.REFNUMBER,
        a.DATECREATED,
        a.DESCRIPTION,
        e.CATEGORYCODE,
        e.OUTSTANDINGAM_MONAMT,
        e.PREVPAIDAMOUN_MONAMT,
        e.TOTALINCURRED_MONAMT,
        e.LOSSFROMDATE,
FROM 
TABLE_A a
INNER JOIN TABLE_B b ON (b.id = a.id)
INNER JOIN TABLE_C c ON (c.id = b.id)
INNER JOIN TABLE_D d ON
(
   c.i =
   (
      select
      d.id
      FROM TABLE_D
      WHERE TABLE_D.id = c.id
      AND TABLE_D.id =
      (
         select
         max(id)
         from TABLE_D t1
         where c_id = c.id
         and CREATIONDATE =
         (
            select
            max(CREATIONDATE)
            from TABLE_D t2
            where t2.c_id = t1.c_id
         )
      )
   ) 
)

INNER JOIN TABLE_E e ON
(
   d.i =
   (
      select
      e.d_id
      from TABLE_E
      where d_id = d.id
      AND id =
      (
         select
         max(id)
         from e t1
         where e.d_id = d.id
         and CREATIONDAT开发者_如何学PythonE =
         (
            select
            max(CREATIONDATE)
            from TABLE_E t2
            where t2.d_id = t1.d_id
         )
      )
   )
)

This works when I call it on it's own but when I add it to an INNER JOIN I am getting a row for each matching row in table Y.

What I want is the latest record by creationdate and id where xid = id from TABLE_X.

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

"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 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)