Help with the SQL Query filter

Member_2_7967119
Member_2_7967119 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
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

Author

Commented:
I am getting all the results except that they are being sorted by version number .
Naitik GamitSoftware Developer
Top Expert 2015

Commented:
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
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Recommendation to close this question by accepting the above comment as solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial