November 25, 2013 at 1:38 pm
ID,FID,HC,vc,sN
I have to send the records with unique combination HC and VC.
when they are same then I have to pick the record based on maximum updatedate
when updatedated is same the I have to pick the record based on maximum SN
when SN is same the I have to pick the record based on maximum FID.
please suggest way to do this
IDFIDHC VC SNupdateddate
1041100562 500015129748142012-03-13
1041100865 500015129805082012-08-27
1041102406 500015130428302013-04-05
1041102406 500015130428322013-04-05
1041102407 500015130216922013-04-10
1041102408 500015130210632013-04-10
1041102408 500015130210702013-04-10
1041102409 500015130217092013-01-25
1041102979 500015130200702013-01-04
1041103921 500015130342032012-10-23
1041104659 500015130503222013-03-14
1041105309 500015130628082012-03-13
November 25, 2013 at 2:00 pm
Pretty sparse on details but I am pretty sure something like this will work.
select top 1 max(ID), max(FID), HC, vc, Max(sN), MAX(updateddate)
from SomeTable
group by hc, vc
If that doesn't work, please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2013 at 5:31 pm
Sounds to me like you need something similar to this:
WITH SampleData (ID, FID, HC, VC, SN, updateddate) AS
(
SELECT 104,1,100562,5000151,2974814,'2012-03-13'
UNION ALL SELECT 104,1,100865,5000151,2980508,'2012-08-27'
UNION ALL SELECT 104,1,102406,5000151,3042830,'2013-04-05'
UNION ALL SELECT 104,1,102406,5000151,3042832,'2013-04-05'
UNION ALL SELECT 104,1,102407,5000151,3021692,'2013-04-10'
UNION ALL SELECT 104,1,102408,5000151,3021063,'2013-04-10'
UNION ALL SELECT 104,1,102408,5000151,3021070,'2013-04-10'
UNION ALL SELECT 104,1,102409,5000151,3021709,'2013-01-25'
UNION ALL SELECT 104,1,102979,5000151,3020070,'2013-01-04'
UNION ALL SELECT 104,1,103921,5000151,3034203,'2012-10-23'
UNION ALL SELECT 104,1,104659,5000151,3050322,'2013-03-14'
UNION ALL SELECT 104,1,105309,5000151,3062808,'2012-03-13'
)
SELECT ID, FID, HC, VC, SN, updateddate
FROM
(
SELECT ID, FID, HC, VC, SN, updateddate
,rn=ROW_NUMBER() OVER (PARTITION BY HC, VC ORDER BY updateddate DESC, SN DESC, FID DESC)
FROM SampleData
) a
WHERE rn=1;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 25, 2013 at 8:36 pm
@ Sean Lange
Thank you...
November 25, 2013 at 8:38 pm
@dwain.c
Thank you...it worked...:)
SELECT ID, FID, HC, VC, SN, updateddate
FROM
(
SELECT ID, FID, HC, VC, SN, updateddate
,rn=ROW_NUMBER() OVER (PARTITION BY HC, VC ORDER BY updateddate DESC, SN DESC, FID DESC)
FROM SampleData
) a
WHERE rn=1;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply