Post reply

Returning latest revision

  • November 4, 2013 at 5:44 am

    #279551

    Hey there,

    First thanks for looking. 🙂

    I have a table that pulls out the following when i use the sql (select * from table1 where lookup_name='Phil Smythe')

    lookupid, ac_id, Rev_no, Target_Year ,lookup_name, sc,uk,int, Notes

    376 2 1 1 Phil Smythe 22.2900NULL

    722 3 1 1 Phil Smythe 47.5100NULL

    1097 4 1 1 Phil Smythe 46.1700NULL

    1102 5 1 1 Phil Smythe 333Inserted By jamie

    1103 5 2 1 Phil Smythe 23.42test

    I am needing to take this further and pull out just the top rev_no for the ac_id (you can see there is two 5 in the ac_id field)

    My mind has gone blank so any help would be greatly appreciated.

    Thanks

  • November 4, 2013 at 5:50 am

    #1663785

    ROW_NUMBER() OVER (partition by ac_id order by Rev_no DESC) as RevisionNumber

    Then filter for that = 1.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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