CODEWITHSUNDEEP
sqloracle-database
user1173496
user1173496

Reputation: 111

SQl Query : need to get the latest created data in the child records

I have a requirment in which I need to get the latest created data in the child records.

Suppose there are two tables A and B. A is parent and B is child. They have 1:M relation. Both has some columns and B table has one 'created date' column also which holds the created date of the record in table B.

Now, I need to write a query which can fetch all records from A table and it's latest created child record from B table. suppose If two child records are created today in table B for a parent record then the latest one out of them should get fetch.

One record of A table could have many childs, so how can we achive this.

Result should be - Columns of tbl A, Columns of tbl B(Latest created one)

Upvotes: 0

Views: 4279

Answers (4)

Alex Poole
Alex Poole

Reputation: 191445

You can use analytic functions to avoid hitting each table (or specifically B) more than once

Using CTEs to provide dummy data for A and B you can do this:

with A as (
    select 1 as id from dual
    union all select 2 from dual
    union all select 3 from dual
),
B as (
    select 1 as a_id, date '2012-01-01' as created_date, 'First for 1' as value
        from dual
    union all select 1, date '2012-01-02', 'Second for 1' from dual
    union all select 1, date '2012-01-03', 'Third for 1' from dual
    union all select 2, date '2012-02-01', 'First for 2' from dual
    union all select 2, date '2012-02-03', 'Second for 2' from dual
    union all select 3, date '2012-02-01', 'First for 3' from dual
    union all select 3, date '2012-02-03', 'Second for 3' from dual
    union all select 3, date '2012-02-05', 'Third for 3' from dual
    union all select 3, date '2012-02-09', 'Fourth for 3' from dual
)
select id, created_date, value from (
    select a.id, b.created_date, b.value,
        row_number() over (partition by a.id order by b.created_date desc) as rn
    from a
    join b on b.a_id = a.id
)
where rn = 1
order by id;

        ID CREATED_D VALUE
---------- --------- ------------
         1 03-JAN-12 Third for 1
         2 03-FEB-12 Second for 2
         3 09-FEB-12 Fourth for 3

You can select any columns you want from A and B, but you'll need to alias them in the subquery if there are any with the same name in both tables.

You may also need to user rank() or dense_rank() instead of row_number to handle ties appropriately, if you can have child records with the same created date.

Upvotes: 0

Dave S
Dave S

Reputation: 775

This is not the most efficient but will work (SQL Only):

    SELECT [Table_A].[Columns], [Table_B].[Columns]
    FROM [Table_A]
    LEFT OUTER JOIN [Table_B]
    ON [Table_B].ForeignKey = [Table_A].PrimaryKey
    AND [Table_B].PrimaryKey = (SELECT TOP 1 [Table_B].PrimaryKey
                                FROM [Table_B]
                                WHERE [Table_B].ForeignKey = [Table_A].PrimaryKey
                                ORDER BY [Table_B].CREATIONDATE DESC)

Upvotes: 0

Mahi
Mahi

Reputation: 1

Below is the query that can help you out.

select x, y from ( select a.coloumn_TAB_A x, b.coloumn_TAB_B y from TableA a ,
TableB b where a.primary_key=b.primary_key
and a.Primary_key ='XYZ' order by b.created_date desc) where rownum < 2

Here we have two tables A and B, Joined them based on primary keys, order them on created date column of Table B in Descending order.

Use this output as inline view for outer query and select whichever coloumn u want like x, y. where rownum < 2 (that will fetch the latest record of table B)

Upvotes: 0

Keith MacDonald
Keith MacDonald

Reputation: 310

I hope the 'created date' is a DATETIME column. This would give you the most recent child record. Assuming you have a consistent ID in the parent table with the same ParentID in the child table as a foreign key....

select A.*, B.*
from A 
join B on A.ParentID = B.ParentID
join (
  select ParentID, max([created date]) as [created date]
  from B
  group by ParentID
  ) maxchild on A.ParentID = maxchild.ParentID 
where B.ParentID = maxchild.ParentID and B.[created date] = maxchild.[created date]

Upvotes: 1

Related Questions