Question about MAX()

I have a table which has case numbers and version numbers. The same case number has different version numbers. I was wondering if there was a way to select all the case numbers with the highest version numbers. Pretty much what I am looking for is as follows:

TABLECASES:

CaseNumber VersionNum

1234 1

1234 2

1234 3

3567 1

3567 2

Upon running my query..I want something as follows:

CaseNumber VersionNum

1234 3

3567 2

Can sombody point me how to construct the query..thanks

SELECT CASENUMBER, MAX(VERSIONNUM)
FROM TABLECASES
GROUP BY CASENUMBER

It should be that simple,

Roberto Hernández-Pou
http://community.rhpconsulting.net

|||

Thanks a lot for this prompt response. Now suppose I have additional information in this table that differs from version to version..something as follows:

TABLECASES:

CaseNumber VersionNum VersionInfo

1234 1 finapp

1234 2 reopened

1234 3 closed

3567 1 finapp

3567 2 reopened

Now if I use the query that you suggested which is

SELECT CASENUMBER, MAX(VERSIONNUM)
FROM TABLECASES
GROUP BY CASENUMBER

I get the following:

CaseNumber VersionNum

1234 3

3567 2

Now..what I want instead is something like:

CaseNumber VersionInfo

1234 closed

3567 reopened

The version info is contingent upon the version number..is there a way to modify the sql to do that...thanks

|||

SELECT a.CASENUMBER, a.VersionInfo FROM TABLECASES a INNER JOIN (SELECT CASENUMBER, MAX(VERSIONNUM) as maxVersionNum FROM TABLECASES GROUP BY CASENUMBER) b ON a.CaseNumber =b.CaseNumber AND VERSIONNUM=b.maxVERSIONNUM

|||If you use SQL Server 2005, you can also do this:

SELECT a.CASENUMBER, a.VersionInfo FROM

(SELECT CASENUMBER, VersionInfo , ROW_Number() OVER(partition by CASENUMBER ORDER BY VERSIONNUM DESC) as rankNum

FROM TABLECASES) AS a

WHERE a.rankNum=1