Script to pull out top revision number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Script to pull out top revision number

Author

Topic

jamiesw
Starting Member

6 Posts

Posted - 2013-11-04 : 07:32:25

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.29 0 0 NULL
722 3 1 1 Phil Smythe 47.51 0 0 NULL
1097 4 1 1 Phil Smythe 46.17 0 0 NULL
1102 5 1 1 Phil Smythe 3 3 3 Inserted By jamie
1103 5 2 1 Phil Smythe 2 3.4 2 test

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
J

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 07:39:19

[code]
select lookupid,ac_id,Rev_no,Target_Year,lookup_name, sc, uk, int, Notes
from
(
select *,
row_number() over (partition by ac_id,Target_Year,lookup_name order by Rev_no desc) as Rn
from table1
where lookup_name='Phil Smythe'
)t
where Rn = 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources