December 26, 2008 at 10:05 am
Not a problem, Jude, glad we could help.
I did figure out the issue with mine though, which is only an issue with multiple appointmentID's being the same. So, the derived table won't work with multiple times in common, mine won't work with multiple appointmentID's in common. If you think both of these could happen, let us know and we'll get a solution that can handle both.
December 26, 2008 at 10:16 am
Just to tie up the loose ends, this solution should be correct, and handle all situations.
;WITH MA( Apprentice_id, Agreement_ID, [Start_Date], RN )
AS
(SELECTApprentice_ID,
Agreement_ID,
[Start_Date],
ROW_NUMBER() OVER (PARTITION BY Apprentice_ID ORDER BY [Start_Date] DESC) RN
FROM apprenticeshiptraining.tbl_apprentice_agreement)
SELECT Apprentice_ID, Agreement_ID, [Start_Date]
FROM MA
WHERE RN = 1
December 26, 2008 at 10:45 am
Garadin (12/26/2008)
With the way that derived table is written, if you have more than 1 appointment that has the MAX(date), you will get multiple returns.
Seth,
The way I understand the problem, this is the goal, return the "most recent" row for each apprentice, agreement combination.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 26, 2008 at 12:06 pm
Jack Corbett (12/26/2008)
Garadin (12/26/2008)
With the way that derived table is written, if you have more than 1 appointment that has the MAX(date), you will get multiple returns.Seth,
The way I understand the problem, this is the goal, return the "most recent" row for each apprentice, agreement combination.
I believe she wants the most recent agreement per apprentice, not the most recent date for each agreement.
Q-girl
What I want is the agreement_id that goes with the MAX(start_date). For each apprentice_id I have several agreement_id's. I want to pull the one with the latest start_date.
December 26, 2008 at 12:50 pm
Just to clarify, the apprentice_id has multiple agreement_ids. I was trying to return the most recent (by start_date) agreement_id for each apprentice_id. I initially tried to do it by an identity key but the records were converted from another system and the identity keys did not match the true age of the record.
Again, thanks for all your help and advice today!
Jude
December 26, 2008 at 1:49 pm
After some tests, ROW_NUMBER is still slow. I ran both queries on a real table and came up with 76% of the cost vs 24% of the cost when I ran them both. The sort is 72% of the cost for the row_number query. A small adjustment to the derived table makes it far superior (at least in the situation I tested it in). The table I ran the queries against could have had RowNumbers > 1000, so the situation isn't 100% equal, but close enough.
To make sure you only ever end up with 1 row even with multiple agreements on the same day, just mod it like this:
SELECTA.Apprentice_ID,
MAX(MA.Agreement_ID) Agreement_ID, -- Max Handles 2+ agreements from the same Day
MA.max_date
FROM apprenticeshiptraining.tbl_apprentice_agreement A
INNER JOIN (SELECT Apprentice_ID, MAX([Start_Date])
FROM apprenticeshiptraining.tbl_apprentice_agreement
GROUP BY Apprentice_ID) MA
ON A.Apprentice_ID = MA.Apprentice_ID AND
A.[Start_Date] = MA.[Start_Date]
GROUP BY A.Apprentice_ID, MA.Max_Date
[Edit]
I think the row_number solution would have been good if you needed something like the "Next to most" recent, or the third most recent etc. rather than just the MAX.
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply