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 :