Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Picking unique records Expand / Collapse
Author
Message
Posted Monday, November 25, 2013 1:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:36 PM
Points: 14, Visits: 13

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


ID FID HC VC SN updateddate

104 1 100562 5000151 2974814 2012-03-13
104 1 100865 5000151 2980508 2012-08-27
104 1 102406 5000151 3042830 2013-04-05
104 1 102406 5000151 3042832 2013-04-05
104 1 102407 5000151 3021692 2013-04-10
104 1 102408 5000151 3021063 2013-04-10
104 1 102408 5000151 3021070 2013-04-10
104 1 102409 5000151 3021709 2013-01-25
104 1 102979 5000151 3020070 2013-01-04
104 1 103921 5000151 3034203 2012-10-23
104 1 104659 5000151 3050322 2013-03-14
104 1 105309 5000151 3062808 2012-03-13
Post #1517446
Posted Monday, November 25, 2013 2:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1517456
Posted Monday, November 25, 2013 5:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1517501
Posted Monday, November 25, 2013 8:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:36 PM
Points: 14, Visits: 13
@ Sean Lange

Thank you...
Post #1517513
Posted Monday, November 25, 2013 8:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:36 PM
Points: 14, Visits: 13
@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;
Post #1517514
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse