Using Row_Number() as ID field to select the newest record

  • I am asking the question as I begin the research on how to do this because I am not sure if I will figure this out on my own. I have not used the Row_Number() function that often, and most of those times it was after an old mentor of mine had written it for me.

    Here's what I am trying to achieve. I am re-working a nested derived table:

    Select PropertyStatusDate

    ,comments

    ,rmVvlPropertyStatus

    ,rvlPropertyStatus.rvlpropertyid

    ,rvlPropertyStatusID

    From dbo.rvlPropertyStatus Inner Join

    (Select Max(PropertyStatusDate) As maxDate

    ,rvlPropertyID

    From dbo.rvlPropertyStatus

    Group By rvlPropertyID) As maxStatus

    On rvlPropertyStatus.PropertyStatusDate = maxStatus.maxDate

    And rvlPropertyStatus.rvlPropertyID = maxStatus.rvlPropertyID

    In my system the joined derived table return 17,635 records, but the entire join returns 17,637 records. I believe this is being caused by 2 instances where the same property ID has two instances of the same date.

    My thought is to use Row_Number() to accomplish this. I believe I know how to do this, but that is still in the conceptual phase.

    My question is in two parts:

    1. - The obvious question of how. I will be working on that as soon as I post this, but I don't have an exceeding level of confidence in my skills with this function yet.

    2. - Is there a better means of achieving my goal? In other words what are the places where this is going to turn around and bite me that I haven't thought of yet?

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • Ok, I have a solution for my first question. I am sure there must be a better way to do this, but this is what I've got so far:

    Exec dbo.upObjectQuickDrop

    @ObjectName = 'TempDataHolder'

    ,@ObjectType = 'Table'

    ,@Schema = 'TempDB';

    Go

    With CTE

    (NestRowNumber

    ,rvlPropertyID)

    As

    (Select Row_Number() Over(Order By rvlPropertyID)

    ,rvlPropertyID

    From dbo.rvlPropertyStatus)

    Select Max(NestRowNumber) As RowNumber

    ,rvlPropertyID

    Into #TempDataHolder

    From CTE

    Group By rvlPropertyID

    Order By rvlPropertyID;

    Go

    Exec dbo.upObjectQuickDrop

    @ObjectName = 'TempResultsSet'

    ,@ObjectType = 'Table'

    ,@Schema = 'TempDB';

    Go

    Select (Row_Number() Over(Order By rvlPropertyID)) As RowNumber

    ,PropertyStatusDate

    ,comments

    ,rmVvlPropertyStatus

    ,rvlPropertyStatus.rvlpropertyid

    ,rvlPropertyStatusID

    Into #TempResultsSet

    From dbo.rvlPropertyStatus

    Select TRS.RowNumber

    ,PropertyStatusDate

    ,comments

    ,rmVvlPropertyStatus

    ,TRS.rvlPropertyID

    ,rvlPropertyStatusID

    From #TempResultsSet TRS Inner Join #TempDataHolder TDH

    On TRS.rvlPropertyID = TDH.rvlPropertyID

    And TRS.RowNumber = TDH.RowNumber

    Any thoughts? Any ideas where I may be overlooking a potential logic bomb?

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • You could read about windowing function and learn how ti use them.

    Your example can be solved like this:

    SELECT ss.*

    FROM

    (

    Select s.PropertyStatusDate

    ,s.comments

    ,s.rmVvlPropertyStatus

    ,s.rvlPropertyStatus.rvlpropertyid

    ,s.rvlPropertyStatusID

    ,rnum = ROW_NUMBER() OVER(PARTITION BY s.rvlPropertyID ORDER BY s.PropertyStatusDate DESC)

    From dbo.rvlPropertyStatus s

    ) ss

    WHERE ss.rnum = 1

    Please use aliases.

    Kind regards,

    Vedran

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

Viewing 3 posts - 1 through 2 (of 2 total)

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