August 4, 2017 at 8:44 am
Hi Everyone,
I have a query like this:
SELECT ORDERS.GFNo,
OrderCategories.Description,
ORDERS.OpenDate,TO
TOMain.Description AS TOMain,
AGENT.Name,
ORDERS.OtherFileNum,
ORDERS.CommitDate,
ORDERS.FTReceived,
--t1.max_date,
--t1.notes,
--d.Department,
--d.ID,
CASE
WHEN AGENT.Name LIKE 'KML%' Then Dateadd(dd, 4, ORDERS.OpenDate)
WHEN AGENT.Name LIKE 'Service%' Then Dateadd(dd, 2, ORDERS.OpenDate)
ELSE Dateadd(dd, 5, ORDERS.OpenDate)
END AS DeadlineDate
FROM ORDERS
INNER JOIN TOMain ON TOMain.TONum = ORDERS.TONum
INNER JOIN OrderCategories ON ORDERS.OrderCategory = OrderCategories.OrderCategory
INNER JOIN AGENT ON ORDERS.Referral = AGENT.AgentNum
-- INNER JOIN
--(select GFNo, CONVERT(nvarchar(max), Notes) AS Notes, Max(OrdNotesID) AS Ord, Max(Ndate) as max_date
-- FROM ORDNOTES
-- GROUP BY GFNO, CONVERT(nvarchar(max), Notes)) as t1 on ORDERS.GFNO = t1.GFNo and t1.max_date = t1.max_date
-- INNER JOIN
-- (SELECT GFNo, Department, MAX(IDNum) as ID
--FROM FTLog
--GROUP BY GFNo,Department) AS d ON ORDERS.GFNO = d.GFNo and d.ID = d.id
--WHERE d.Department IN ('Abstracting', 'Abstract OS NJ' ,'ABSTRACT OS PA', 'Delivery', 'Examination', 'Final Abstract', 'Origination', 'Typing')
ORDER BY ORDERS.GFNo
Now, Commenting out the INNER JOINs in the FROM clause gives me the records I want. Theres only 1 record for each entry. Now here's the problem....
When I add the INNER JOINS to get a MAX value, I start to get multiple records for each GFNO(which is the primary key). The other tables is a 1-many, so there could be more than one entry for the GFNo. I only want to bring back the most recent record of each GFNo from the other tables, but it looks like i am bringing back all of them.
Can someone look at my code and give me some insight as to why I am getting all the records and not just the most recent? It has to be in the inner joins somewhere but I can't out my finger on it.
thanks!
August 4, 2017 at 8:52 am
Please post the DDL (CREATE TABLE statement) for the table(s) involved, sample data (INSERT INTO statements) for each table that is representative of your problem domain (and should exhibit the behavior you describe), and the expected results based on the sample data you provide. The sample data should not be more than 20 rows of data and should not be production data.
Test all the scripts you create in an empty sandbox database to be sure they run with no problems.
This information will allow us to setup our own sandbox and help you with your problem. Also, you will get tested code in return.
August 4, 2017 at 9:25 am
Quick suggestion - try APPLY with TOP:
FROM ORDERS
INNER JOIN TOMain ON TOMain.TONum = ORDERS.TONum
INNER JOIN OrderCategories ON ORDERS.OrderCategory = OrderCategories.OrderCategory
INNER JOIN AGENT ON ORDERS.Referral = AGENT.AgentNum
CROSS APPLY (
SELECT TOP(1)
CONVERT(nvarchar(max), Notes) AS Notes,
OrdNotesID AS Ord,
Ndate AS max_date
FROM ORDNOTES n
WHERE n.GFNo = ORDERS.GFNO
ORDER BY OrdNotesID DESC
) t1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 4, 2017 at 10:32 am
ChrisM@Work - Friday, August 4, 2017 9:25 AMQuick suggestion - try APPLY with TOP:FROM ORDERS
INNER JOIN TOMain ON TOMain.TONum = ORDERS.TONum
INNER JOIN OrderCategories ON ORDERS.OrderCategory = OrderCategories.OrderCategory
INNER JOIN AGENT ON ORDERS.Referral = AGENT.AgentNum
CROSS APPLY (
SELECT TOP(1)
CONVERT(nvarchar(max), Notes) AS Notes,
OrdNotesID AS Ord,
Ndate AS max_date
FROM ORDNOTES n
WHERE n.GFNo = ORDERS.GFNO
ORDER BY OrdNotesID DESC
) t1
The cross applys work perfectly! thank you!
August 4, 2017 at 11:13 am
There are two main options for this, and which one performs better depends on the typical number of records on the many side of the relationship. The CROSS APPLY works best when that number is higher, but for lower numbers, you might be better off using a CTE with a ROW_NUMBER.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply