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

Need to find date of Nth crash for every car_make in every country Expand / Collapse
Author
Message
Posted Wednesday, January 15, 2014 6:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 8:31 AM
Points: 3, Visits: 3
I have a table similar to the one below. I need to find:

date of Nth crash for every Car_Make in every country

Any thoughts? Thanks


Country Crash_Date Car_Make

US 10/19/2012 Toyota
US 10/18/2012 Rover
US 10/17/2012 Honda
US 10/16/2012 Rover
US 10/11/2012 Toyota
JP 11/09/2010 Honda
JP 11/08/2010 Rover
JP 11/05/2010 Honda
JP 11/04/2010 Rover
JP 11/03/2010 Honda
JP 10/01/2010 Honda
GB 05/01/2011 Rover
GB 05/05/2011 Honda
GB 05/03/2011 Rover
GB 05/05/2011 Honda
GB 03/05/2011 Honda
GB 07/05/2011 Rover
GB 05/07/2011 Honda
GB 05/09/2011 Rover
GB 01/01/2011 Rover
Post #1531366
Posted Wednesday, January 15, 2014 7:49 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 710, Visits: 4,531
Not sure if this will help if you're querying a DW... this is one way:

SELECT c.CarMake
, c.Country
, c.EventDate
, c.RowNum
FROM
(SELECT CarMake
, Country
, EventDate
, ROW_NUMBER() OVER (PARTITION BY CarMake, Country ORDER BY CarMake, Country) AS RowNum
FROM #Crashes) c
WHERE c.RowNum=2;
Post #1531370
Posted Thursday, January 16, 2014 11:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 8:31 AM
Points: 3, Visits: 3
it works fine - thanks a lot!
Post #1531745
Posted Thursday, January 16, 2014 3:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 7,121, Visits: 15,024
by the way - you will want to incorporate the event_date into the ORDER BY, otherwise the row_number() will essentially assign numbers within a group ad-hoc (i.e. not exactly "at random", but it won't always assign the numbers in the same way). So just make sure that whatever you're using the ORDER BY gives you a good enough ordering (so that if it does include multiple entries with the same ORDER BY "key", you're comfortable with any of them being the one being returned).



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1531846
Posted Thursday, January 16, 2014 3:54 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: Yesterday @ 10:33 PM
Points: 3,374, Visits: 7,296
Matt Miller (#4) (1/16/2014)
by the way - you will want to incorporate the event_date into the ORDER BY, otherwise the row_number() will essentially assign numbers within a group ad-hoc (i.e. not exactly "at random", but it won't always assign the numbers in the same way). So just make sure that whatever you're using the ORDER BY gives you a good enough ordering (so that if it does include multiple entries with the same ORDER BY "key", you're comfortable with any of them being the one being returned).


To add a little bit more, the columns CarMake and Country are not needed in the ORDER BY because they're in the PARTITION BY.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1531853
Posted Friday, January 17, 2014 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 8:31 AM
Points: 3, Visits: 3
thank you - all great
Post #1532091
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse