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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 773, Visits: 5,005
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 @ 3:57 PM
Points: 7,064, Visits: 15,270
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 @ 2:04 PM
Points: 3,572, Visits: 8,008
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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