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
Here is the query which returns the records with the highest version #
select docnum, MAX(version) as maxversion
from MHGROUP.DOCMASTER
group by DOCNUM
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?
re:> but can't figure out how to get the other columns.
What are the other columns?
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
re:> but can't figure out how to get the other columns.
What are the other columns?
ASKER
In the sample data shown below I would want all the columns but only the highlighted records
sample-data.png
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked perfectly. Thanks for the help.
In order to have a better answer it would be most helpful to have a small sample of the data