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 2005 Forums
 Transact-SQL (2005)
 Finding a maximum value per account
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

demsoft
Starting Member

United Kingdom
1 Posts

Posted - 08/26/2014 :  06:10:08  Show Profile  Reply with Quote
Hi everyone
I'm stuck trying to get some useful information out of a line-of-business system we're using. Essentially there's a table I want to use in order to query onother one, but before I can get that far I need to get the 'latest revision' version for an account.

The table is pretty big & seems poorly designed, but the 3 fields I'm currently interested in are Account, Revision & Display. Account is an account number, Revision and Display are integers (stored as text). NONE of thenm are unique, although the combination of Account & Revision is.
What I want to do is find the highest revision number for each account, & then use the associated Display to link to my other table(s). The SQL I've got so far is :

select account, revision, display
from histhead o
where revision = (
select max(revision)
from histhead
where revision = o.revision)

Unfortunately, because there are multiple accounts of the same value it brings back something like :

account revision display
005598 001 201005 7843
005598 001 310358 17618
005598 002 201005 7844
005598 002 999999 17619
005598 003 201005 7845
005598 003 999999 17620
007475 200810 962
007475 200900 1252
007475 999999 16910
007641 201003 7039
007641 201311 25958

Whereas what I really want is just :

account revision display
005598 001 310358 17618
005598 002 999999 17619
005598 003 999999 17620
007475 999999 16910
007641 201311 25958

Can anyone tell me how to do that, please?

thanks

Mark

gbritton
Flowing Fount of Yak Knowledge

1445 Posts

Posted - 08/26/2014 :  08:09:38  Show Profile  Reply with Quote
I'm confused by the example data:


account	revision	display
005598 001	201005	7843


has three column headings but four numbers. What goes with what? Is there a missing column heading?
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.08 seconds. Powered By: Snitz Forums 2000