T-SQL Subquery on Latest Date with InnerJoin

Question

I want to do a similiar thing like this guy: T-SQL Subquery Max(Date) and Joins

I have to do this with an n:m relation.

So the layout is:

tbl_Opportunity
tbl_Opportunity_tbl_OpportunityData
tbl_OpportunityData

So as you see there is an intersection table which connects opportunity with opportunitydata. For every opportunity there are multiple opportunity datas. In my view i only want a list with all opportunites and the data from the latest opportunity datas.

I tried something like this:

SELECT     
    dbo.tbl_Opportunity.Id, dbo.tbl_Opportunity.Subject, 
    dbo.tbl_User.UserName AS Responsible, dbo.tbl_Contact.Name AS Customer, 
    dbo.tbl_Opportunity.CreationDate, dbo.tbl_Opportunity.ActionDate AS [Planned Closure], 
    dbo.tbl_OpportunityData.Volume, 
    dbo.tbl_OpportunityData.ChangeDate, dbo.tbl_OpportunityData.Chance
FROM         
    dbo.tbl_Opportunity 
INNER JOIN
    dbo.tbl_User ON dbo.tbl_Opportunity.Creator = dbo.tbl_User.Id 
INNER JOIN
    dbo.tbl_Contact ON dbo.tbl_Opportunity.Customer = dbo.tbl_Contact.Id 
INNER JOIN
    dbo.tbl_Opprtnty_tbl_OpprtnityData ON dbo.tbl_Opportunity.Id = dbo.tbl_Opprtnty_tbl_OpprtnityData.Id 
INNER JOIN
    dbo.tbl_OpportunityData ON dbo.tbl_Opprtnty_tbl_OpprtnityData.Id2 = dbo.tbl_OpportunityData.Id

The problem is my view now includes a row for every opportunity data, since I don't know how to filter that I only want the latest data.

Can you help me? is my problem description clear enough?

thank you in advance :-) best wishes, laurin

I would **strongly** suggest to use meaningful **table aliases** to make your query more readable! — marc_s, Oct 29 '11 at 07:48

xanatos · Accepted Answer · 2011-10-29T07:52:29.387

; WITH Base AS (
    SELECT     dbo.tbl_Opportunity.Id, dbo.tbl_Opportunity.Subject, dbo.tbl_User.UserName AS Responsible, dbo.tbl_Contact.Name AS Customer, 
                      dbo.tbl_Opportunity.CreationDate, dbo.tbl_Opportunity.ActionDate AS [Planned Closure], dbo.tbl_OpportunityData.Volume, 
                      dbo.tbl_OpportunityData.ChangeDate, dbo.tbl_OpportunityData.Chance
    FROM         dbo.tbl_Opportunity INNER JOIN
                      dbo.tbl_User ON dbo.tbl_Opportunity.Creator = dbo.tbl_User.Id INNER JOIN
                      dbo.tbl_Contact ON dbo.tbl_Opportunity.Customer = dbo.tbl_Contact.Id INNER JOIN
                      dbo.tbl_Opprtnty_tbl_OpprtnityData ON dbo.tbl_Opportunity.Id = dbo.tbl_Opprtnty_tbl_OpprtnityData.Id INNER JOIN
                      dbo.tbl_OpportunityData ON dbo.tbl_Opprtnty_tbl_OpprtnityData.Id2 = dbo.tbl_OpportunityData.Id    
)

, OrderedByDate AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY ChangeDate DESC) RN FROM Base
)

SELECT * FROM OrderedByDate WHERE RN = 1

To make it more readable I'm using CTE (the WITH part). In the end the real "trick" is doing a ROW_NUMBER() partitioning the data by tbl_Opportunity.Id and ordering the partitions by ChangeDate DESC (and I call it RN). Clearly the maximum date in each partition will be RN = 1 and then we filter it by RN.

Without using CTE it will be something like this:

SELECT * FROM (
    SELECT     dbo.tbl_Opportunity.Id, dbo.tbl_Opportunity.Subject, dbo.tbl_User.UserName AS Responsible, dbo.tbl_Contact.Name AS Customer, 
                      dbo.tbl_Opportunity.CreationDate, dbo.tbl_Opportunity.ActionDate AS [Planned Closure], dbo.tbl_OpportunityData.Volume, 
                      dbo.tbl_OpportunityData.ChangeDate, dbo.tbl_OpportunityData.Chance,
                      ROW_NUMBER() OVER (PARTITION BY dbo.tbl_Opportunity.Id ORDER BY dbo.tbl_OpportunityData.ChangeDate DESC) RN
    FROM         dbo.tbl_Opportunity INNER JOIN
                      dbo.tbl_User ON dbo.tbl_Opportunity.Creator = dbo.tbl_User.Id INNER JOIN
                      dbo.tbl_Contact ON dbo.tbl_Opportunity.Customer = dbo.tbl_Contact.Id INNER JOIN
                      dbo.tbl_Opprtnty_tbl_OpprtnityData ON dbo.tbl_Opportunity.Id = dbo.tbl_Opprtnty_tbl_OpprtnityData.Id INNER JOIN
                      dbo.tbl_OpportunityData ON dbo.tbl_Opprtnty_tbl_OpprtnityData.Id2 = dbo.tbl_OpportunityData.Id    
) AS Base WHERE RN = 1

I have absolutely now idea what you are doing...but i'm impressed. it just works. I'm trying to get through this. Maybe i come with some further questions. But can you tell me: is there a way to do this without having RN in output? — LaurinSt, Oct 29 '11 at 08:16
In the final `SELECT`, instead of `SELECT *` you do a `SELECT` of the columns you need. — xanatos, Oct 29 '11 at 08:18

Vadim Loboda · Answer 2 · 2011-11-18T17:05:16.273

The statement can be simplified for one more step further:

SELECT TOP 1 WITH TIES
    dbo.tbl_Opportunity.Id, dbo.tbl_Opportunity.Subject, dbo.tbl_User.UserName AS Responsible, 
    dbo.tbl_Contact.Name AS Customer, dbo.tbl_Opportunity.CreationDate, 
    dbo.tbl_Opportunity.ActionDate AS [Planned Closure], dbo.tbl_OpportunityData.Volume, 
    dbo.tbl_OpportunityData.ChangeDate, dbo.tbl_OpportunityData.Chance
FROM 
    dbo.tbl_Opportunity INNER JOIN
    dbo.tbl_User ON dbo.tbl_Opportunity.Creator = dbo.tbl_User.Id INNER JOIN
    dbo.tbl_Contact ON dbo.tbl_Opportunity.Customer = dbo.tbl_Contact.Id INNER JOIN
    dbo.tbl_Opprtnty_tbl_OpprtnityData ON dbo.tbl_Opportunity.Id = dbo.tbl_Opprtnty_tbl_OpprtnityData.Id INNER JOIN
    dbo.tbl_OpportunityData ON dbo.tbl_Opprtnty_tbl_OpprtnityData.Id2 = dbo.tbl_OpportunityData.Id    
ORDER BY 
    ROW_NUMBER() OVER (PARTITION BY dbo.tbl_Opportunity.Id ORDER BY dbo.tbl_OpportunityData.ChangeDate DESC);

Thank you, this works also great. I have one further questions. I should filter out the items where tbl_Opportunity.m__object_state = 0. but i cant get this to work. where should i insert this where clause? — LaurinSt, Nov 02 '11 at 10:03

T-SQL Subquery on Latest Date with InnerJoin

2 Answers2