Avatar of Member_2_7967119
Member_2_7967119
 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.
Microsoft SQL Server

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Doc_1.png
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Vitor Montalvão

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