Help with the SQL Query filter

I am having a table with 4 columns

DOCUMENT_TITLE,
DOC_REFERENCE_NUM,
DATE_OF_SUBMISSION,
VERSION_NUMBER

The Version Number column hold the version of the document identified by the document title.
I will share the record content to explain what I wish to retrieve.

DOCUMENT_TITLE,DOC_REFERENCE_NUM,DATE_OF_SUBMISSION,VERSION_NUMBER

'SAP Material Management','SAP/002-01','1/1/2017',01
'SAP Material Management','SAP/002-03','2/1/2017',03
'SAP Material Management','SAP/002-04','3/1/2017',04
'DOCUMENTUM Guide','DOC/001-04,'12/1/2016',04
'DOCUMENTUM Guide','DOC/001-01,'1/1/2017',01
'SAP ABAP','SAP1/002-01','3/1/2017',04
'SAP ABAP','SAP1/002-02','3/1/2017',04
'SAP ABAP','SAP1/002-03','3/1/2017',04

As you see the DOC_REFERENCE_NUM has the contain the DOC REFERENCE With the VERSION .NUMBER.

I want the select query to retrieve only the version number with the highest value. In the sample data, the results should be
'SAP Material Management','SAP/002-04','3/1/2017',04 and
'DOCUMENTUM Guide','DOC/001-04,'12/1/2016',04

based on the criteria being passed (ie DOC_REFERENCE_NUM ='SAP/002' for the first result) and DOC_REFERENCE_NUM ='DOC/001' for the second result.

SELECT DOCUMENT_TITLE,DOC_REFERENCE_NUM,DATE_OF_SUBMISSION,VERSION_NUMBER
FROM LIBRARY_SUBMISSIONS WHERE
 DOCUMENT_TITLE like '%SAP%'

should retrieve the following results
'SAP Material Management','SAP/002-04','3/1/2017',04
'SAP ABAP','SAP1/002-03','3/1/2017',04



How can I achieve the filter.
Member_2_7967119Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
You will need to GROUP BY the DOCUMENT_TITLE and get the top 1 row from that record set so you should have a query like below:

SELECT DOCUMENT_TITLE,DOC_REFERENCE_NUM,MAX(DATE_OF_SUBMISSION),MAX(VERSION_NUMBER)
FROM LIBRARY_SUBMISSIONS
WHERE
 DOCUMENT_TITLE like '%SAP%'
Group by DOCUMENT_TITLE
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_2_7967119Author Commented:
I am getting all the results except that they are being sorted by version number .
0
Naitik GamitSoftware DeveloperCommented:
Try As :
 SELECT DISTINCT [@tbl].DOCUMENT_TITLE,DOC_REFERENCE_NUM,VERSION_NUMBER FROM @tbl
 INNER JOIN (SELECT MAX(VERSION_NUMBER) AS MAX_VER_NO,MAX(DOC_REFERENCE_NUM) AS MAX_REF_NUM,DOCUMENT_TITLE FROM @tbl GROUP BY DOCUMENT_TITLE) AS TBL ON TBL.DOCUMENT_TITLE = [@tbl].DOCUMENT_TITLE 
 AND TBL.MAX_VER_NO = VERSION_NUMBER  AND TBL.MAX_REF_NUM = DOC_REFERENCE_NUM
 WHERE [@tbl].DOCUMENT_TITLE like '%SAP%'

Open in new window

This will return as below image
Doc_1.png
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The GROUP BY should be the correct answer for you. No need for extra joins.
Just want to add that lcohan missed one column in his GROUP BY statement:
Group by DOCUMENT_TITLE, DOC_REFERENCE_NUM

No points for this comment, please.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comment as solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.