Return one row in an INNER JOIN

Question

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

The rows in TABLE_D and TABLE_E can have identical creation dates, so I get MAX (createdate) first and then MAX (id) from this set. Here is my full request:

SELECT
a.id as A_ID,
b.id as B_ID,
c.id as C_ID,
d.id as D_ID,
e.id as E_ID,
d.CREATIONDATE,
a.REFNUMBER,
a.DATECREATED,
a.INFO,
e.COST,
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
(
   ci =
   (
      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
(
   di =
   (
      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 CREATIONDATE =
         (
            select
            max (CREATIONDATE)
            from TABLE_E t2
            where t2.d_id = t1.d_id
         )
      )
   )
)

My subquery is to get all rows with max createdate and that max id works fine when I call it, but when I add it to INNER JOIN (see above), I get a row for each corresponding row in table D and Table E. I want one row on TABLE_A.id to show only the last row from TABLE_D, associated with TABLE_C, and the last of TABLE_E, associated with TABLE_D.

, id :

--------------------------------------------------------------------------
A_ID            B_ID            C_ID            D_ID            E_ID
--------------------------------------------------------------------------
1               101             201             301             401
1               101             201             301             402    
1               101             201             301             403    
1               101             201             302             404
1               101             201             302             405    
1               101             201             302             406    

:

--------------------------------------------------------------------------
A_ID            B_ID            C_ID            D_ID            E_ID
--------------------------------------------------------------------------
1               101             201             302             406

.

Answer 1

ROW_NUMBER(), oracle11g

SELECT *
FROM 
(
SELECT
    a.id as A_ID,b.id as B_ID,c.id as C_ID,d.id as D_ID,e.id as E_ID,
    d.CREATIONDATE,a.REFNUMBER,a.DATECREATED,a.INFO,e.COST,
    row_number() over (
        partition by a.id, b.id, c.id
        order by d.CREATIONDATE DESC, d.id desc, e.CREATIONDATE DESC, e.id desc) RN
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 d.c_id = c.id
INNER JOIN TABLE_E e ON e.d_id = d.id
) N
WHERE RN = 1

, PARTITION ORDER BY .

partition by a.id, b.id, c.id
    => start numbering from 1 again when any one of these changes
order by d.CREATIONDATE DESC, d.id desc, e.CREATIONDATE DESC, e.id desc)
    => number the rows in this order

Answer 2

, .

SELECT
a.id as A_ID,
b.id as B_ID,
c.id as C_ID,
(select max(d.id) from d where d.id =c.id) as d_id)
FROM 
TABLE_A a
INNER JOIN TABLE_B b ON (b.id = a.id)
INNER JOIN TABLE_C c ON (c.id = b.id)

Answer 3

WHERE ROWNUM < 2.

SELECT * FROM
(/* Your SQL */)
WHERE ROWNUM < 2

Answer 4

, ?

SELECT TOP 1
a.id as A_ID,
b.id as B_ID,
c.id as C_ID,
d.id as D_ID,
e.id as E_ID,
d.CREATIONDATE,
a.REFNUMBER,
a.DATECREATED,
a.INFO,
e.COST,
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 = d.id)
INNER JOIN TABLE_E e ON ( d.i = e.id )
ORDER BY d.creationdate DESC, e.creationdate DESC