We help IT Professionals succeed at work.

Help with the SQL Query filter

72 Views
Last Modified: 2017-04-11
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

Database Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
I am getting all the results except that they are being sorted by version number .
Naitik GamitSoftware Developer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2017

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions