CODEWITHSUNDEEP
sql-servert-sql
Callum P. Robertson
Callum P. Robertson

Reputation: 23

SQL Join one-to-many tables, selecting only most recent entries

This is my first post - so I apologise if it's in the wrong seciton!

I'm joining two tables with a one-to-many relationship using their respective ID numbers: but I only want to return the most recent record for the joined table and I'm not entirely sure where to even start!

My original code for returning everything is shown below: 

SELECT table_DATES.[date-ID], *
FROM table_CORE LEFT JOIN table_DATES ON [table_CORE].[core-ID] =     table_DATES.[date-ID]
WHERE table_CORE.[core-ID] Like '*'
ORDER BY [table_CORE].[core-ID], [table_DATES].[iteration];

This returns a group of records: showing every matching ID between table_CORE and table_DATES:

table_CORE date-ID iteration
1   1   1
1   1   2
1   1   3
2   2   1
2   2   2
3   3   1
4   4   1

But I need to return only the date with the maximum value in the "iteration" field as shown below

table_CORE date-ID iteration  Additional data
1   1   3    MoreInfo
2   2   2    MoreInfo
3   3   1    MoreInfo
4   4   1    MoreInfo

I really don't even know where to start - obviously it's going to be a JOIN query of some sort - but I'm not sure how to get the subquery to return only the highest iteration for each item in table 2's ID field?

Hope that makes sense - I'll reword if it comes to it!

--edit-- I'm wondering how to integrate that when I'm needing all the fields from table 1 (table_CORE in this case) and all the fields from table2 (table_DATES) joined as well?

Both tables have additional fields that will need to be merged.

I'm pretty sure I can just add the fields into the "SELECT" and "GROUP BY" clauses, but there are around 40 fields altogether (and typing all of them will be tedious!)

Upvotes: 2

Views: 125

Answers (3)

paparazzo
paparazzo

Reputation: 45096

select * 
from 
(
SELECT table_DATES.[date-ID], * 
     , row_number() over (partition by table_CORE date-ID order by iteration desc) as rn
FROM table_CORE 
LEFT JOIN table_DATES 
       ON [table_CORE].[core-ID] =  table_DATES.[date-ID]
WHERE table_CORE.[core-ID] Like '*'
)  tt
where tt.rn = 1
ORDER BY [core-ID]

Upvotes: 0

tarheel
tarheel

Reputation: 4797

Just to make sure that I have everything you’re asking for right, I am going to restate some of your question and then answer it.

Your source tables look like this:

table_core:

table_core_output

table_dates:

table_dates_output

And your outputs are like this:

Current:

current_joined_output

Desired:

desired_output

In order to make that happen all you need to do is use a subquery (or a CTE) as a “cross-reference” table. (I used temp tables to recreate your data example and _ in place of the - in your column names).

--Loading the example data
create table #table_core
    (
        core_id int not null
    )

create table #table_dates
    (
        date_id int not null
        , iteration int not null
        , additional_data varchar(25) null
    )

insert into #table_core values (1), (2), (3), (4)

insert into #table_dates values (1,1, 'More Info 1'),(1,2, 'More Info 2'),(1,3, 'More Info 3'),(2,1, 'More Info 4'),(2,2, 'More Info 5'),(3,1, 'More Info 6'),(4,1, 'More Info 7')

--select query needed for desired output (using a CTE)
; with iter_max as 
    (
        select td.date_id
        , max(td.iteration) as iteration_max
        from #table_dates as td
        group by td.date_id
    )
select tc.*
, td.*
from #table_core as tc
left join iter_max as im on tc.core_id = im.date_id
inner join #table_dates as td on im.date_id = td.date_id
                             and im.iteration_max = td.iteration

Upvotes: 0

Paul Andrew
Paul Andrew

Reputation: 3253

Try using the MAX aggregate function like this with a GROUP BY clause.

SELECT 
    [ID1],
    [ID2], 
    MAX([iteration])
FROM 
    table_CORE 
    LEFT JOIN table_DATES 
        ON [table_CORE].[core-ID] = table_DATES.[date-ID]
WHERE 
    table_CORE.[core-ID] Like '*' --LIKE '%something%' ??
GROUP BY
    [ID1],
    [ID2]

Your example field names don't match your sample query so I'm guessing a little bit.

Upvotes: 2

Related Questions