troubleshooting Question

Help with the SQL Query filter

Avatar of Member_2_7967119
Member_2_7967119 asked on
Microsoft SQL Server
5 Comments1 Solution76 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
lcohan
Database Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros