asked on

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.

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_7967119

ASKER

I am getting all the results except that they are being sorted by version number .

Naitik Gamit

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

Vitor Montalvão

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ão

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