October 9, 2014 at 12:45 pm
I have the following table
DECLARE @TABLE1 TABLE (D_ID int, C_ID int, C_HIST_ID int)
INSERT INTO @TABLE1
VALUES(1000016,71,77),
(1000017,71,547),
(1000017,71,553),
(1000017,71,564),
(1000013,71,565),
(1000017,71,3329),
(1000017,71,3330)
SELECT * FROM @TABLE1 ORDER BY C_HIST_ID
D_ID C_IDC_HIST_ID
10000167177
100001771547
100001771553
100001771564
100001371565
1000017713329
1000017713330
I was able to get the following result
SELECT D_ID,C_ID,C_HIST_ID
FROM @TABLE1 ACH
WHERE ACH.C_ID= 71
AND ACH.C_HIST_ID IN (SELECT MAX(ACH1.C_HIST_ID) FROM @TABLE1 ACH1
WHERE ACH1.C_ID = ACH.C_ID
AND ACH.D_ID = ACH1.D_ID )
ORDER BY C_HIST_ID
D_ID C_IDC_HIST_ID
10000167177
100001371565
1000017713330
But the result I need is
D_ID C_IDC_HIST_ID
10000167177
100001771564
100001371565
1000017713330
C_HIST_ID = 564, which I'm unable to get it through my query, there is no other way I could think of and that is the reason I'm looking for any suggestions here:-)
October 9, 2014 at 1:19 pm
You're posting on a 2014 forum so maybe other alternatives are available.
Here's one option.
WITH CTE AS(
SELECT * ,
ROW_NUMBER() OVER( ORDER BY C_HIST_ID)
- ROW_NUMBER() OVER( PARTITION BY D_ID ORDER BY C_HIST_ID) grouper
FROM @TABLE1
)
SELECT D_ID, C_ID, MAX( C_HIST_ID) Last_C_HIST_ID
FROM CTE
GROUP BY D_ID, C_ID, grouper
ORDER BY Last_C_HIST_ID
October 9, 2014 at 1:25 pm
Or maybe you want your own version for anything that I can't see in your post.
WITH CTE AS(
SELECT * ,
ROW_NUMBER() OVER( ORDER BY C_HIST_ID)
- ROW_NUMBER() OVER( PARTITION BY D_ID ORDER BY C_HIST_ID) grouper
FROM @TABLE1
)
SELECT D_ID,C_ID,C_HIST_ID
FROM CTE ACH
WHERE ACH.C_ID= 71
AND ACH.C_HIST_ID IN (SELECT MAX(ACH1.C_HIST_ID)
FROM CTE ACH1
WHERE ACH1.C_ID = ACH.C_ID
AND ACH.D_ID = ACH1.D_ID
AND ACH.grouper = ACH1.grouper)
ORDER BY C_HIST_ID;
October 9, 2014 at 1:29 pm
Thank you Luis C
October 9, 2014 at 1:57 pm
You're welcome.
The most important part here is that you understand the code, what it does, how and why it works.
If you have any questions, feel free to ask. If something goes wrong you don't want to say it's just a code you got from the internet. 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply