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

  • goodfaithuser

    SSC Veteran

    Points: 219

    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

  • pietlinden

    SSC Guru

    Points: 62342

    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;

  • goodfaithuser

    SSC Veteran

    Points: 219

    it works fine - thanks a lot!

  • Matt Miller (4)

    SSC Guru

    Points: 124136

    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?

  • Luis Cazares

    SSC Guru

    Points: 183496

    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
  • goodfaithuser

    SSC Veteran

    Points: 219

    thank you - all great

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

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