Post reply

Need to find date of Nth crash for every car_make in every country

  • January 15, 2014 at 6:43 pm

    #302850

    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

    CountryCrash_DateCar_Make

    US10/19/2012Toyota

    US10/18/2012Rover

    US10/17/2012Honda

    US10/16/2012Rover

    US10/11/2012Toyota

    JP11/09/2010Honda

    JP11/08/2010Rover

    JP11/05/2010Honda

    JP11/04/2010Rover

    JP11/03/2010Honda

    JP10/01/2010Honda

    GB05/01/2011Rover

    GB05/05/2011Honda

    GB05/03/2011Rover

    GB05/05/2011Honda

    GB03/05/2011Honda

    GB07/05/2011Rover

    GB05/07/2011Honda

    GB05/09/2011Rover

    GB01/01/2011Rover

  • January 15, 2014 at 7:49 pm

    #1680489

    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;

  • January 16, 2014 at 11:48 am

    #1680806

    it works fine - thanks a lot!

  • January 16, 2014 at 3:23 pm

    #1680898

    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?

  • January 16, 2014 at 3:54 pm

    #1680904

    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.
    General Disclaimer:
    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 17, 2014 at 8:35 am

    #1681097

    thank you - all great

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply