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.
| Author |
Topic |
|
demsoft
Starting Member
1 Post |
 Posted - 2014-08-26 : 06:10:08
|
| Hi everyoneI'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, displayfrom histhead owhere 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 display005598 001 201005 7843005598 001 310358 17618005598 002 201005 7844005598 002 999999 17619005598 003 201005 7845005598 003 999999 17620007475 200810 962007475 200900 1252007475 999999 16910007641 201003 7039007641 201311 25958Whereas what I really want is just :account revision display005598 001 310358 17618005598 002 999999 17619005598 003 999999 17620007475 999999 16910007641 201311 25958Can anyone tell me how to do that, please?thanksMark |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-26 : 08:09:38
|
I'm confused by the example data:account revision display005598 001 201005 7843 has three column headings but four numbers. What goes with what? Is there a missing column heading? |
 |
|
|
|
|
|
Finding a maximum value per account