SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Script to pull out top revision number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jamiesw
Starting Member

United Kingdom
6 Posts

Posted - 11/04/2013 :  07:32:25  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/04/2013 :  07:39:19  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000