Hi There,
I have a situation where I'm joining from a primary table T1 with an "event date", to another table in a common key. The secondary table T2 may contain zero to many rows of relevance to the row in T1, but only those with a date less than the "event date" are relevant, and I only want to show values from the most recent one.
I have struggled to achieve this:
I am using a LEFT OUTER JOIN to the table based on the common key
I am using a PARTITION over the common key - a PersonID - and getting a RowNumber
I'm then as part of my LEFT OUTER JOIN, I'm limting the rows returned to those PRIOR to the T1 table event date.
BUT whatever I do, I can't seem to apply a TOP 1 to the rows returned.
The RowNumber returned varies - if there are more rows later than "event date" in T1, then the RowNumber won't start at 1
And if I try to put a criteria on RowNum - as I understand it the typical style of doing TOP N queries in Oracle - you may be able to tell, I predominantly work in the SQL Server space , this doesn't work, and I get ALL of the rows less than the "event date".
So I am a bit stuck. The reason I need to do things this way, is that the database concerned is a reporting database produced from a system I have little control over, and the queries are being used to produce SQL Server Reporting Services reports, so I essentially have to create my "datasets" in one query.
Any help would be greatly appreciated.
Regards,
Trevor