Link to home
Start Free TrialLog in
Avatar of SBarrharo
SBarrharo

asked on

SQL group by query to return records with highest value

I have a SQL table which lists documents and versions. The primary key is a combination of the DOCNUM field and the VERSION field. I need to return the record with the highest version # for each DOCNUM.  I can get the DOCNUM and VERSION with the group by field but can't figure out how to get the other columns.

Here is the query which returns the records with the highest version #
select docnum, MAX(version) as maxversion
from MHGROUP.DOCMASTER
group by DOCNUM
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

So you need to split the DocNum and use the "same" querying  to retrieve the value you need...
In order to have a better answer it would be most helpful to have a small sample of the data
re> I need to return the record with the highest version # for each DOCNUM

Your query below does what you are asking for: Do you want something different?
select docnum, MAX(version) as maxversion
from MHGROUP.DOCMASTER
group by DOCNUM

Open in new window


re:> but can't figure out how to get the other columns.
What are the other columns?
Avatar of SBarrharo
SBarrharo

ASKER

In the sample data shown below I would want all the columns but only the highlighted records
sample-data.png
Try:
select *
From( Select docnum, ROW_NUMBER() OVER(PARTITION BY DOCNUM ORDER BY version Desc) as ver
from MHGROUP.DOCMASTER) D
Where ver=1

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This worked perfectly. Thanks for the help.