This does not answer your question completely.
Sql:
With afile as (select substr(litem, 1, 7) theitem, substr(litem, 8, 4)
therev
from qtemp/temp where substr(litem,8,4) <> ' ')
Select theitem, max(therev) from afile
Groupb by theitem, order by theitem
This will get you 2 fields
ITEM01- RV04
ITEM02- RV04
ITEM03- RV06
(this was done completely in text in this email so it may not work on first
attempt)
Paul
www.andecosoftware.com
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Steve
Landess
Sent: Wednesday, February 03, 2016 8:07 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Try to figure out how to write a query which will show ONLY the
highest revision number for a given Item number
I have been beating my head on the wall this afternoon, need more brains.
Here is the list I need from the sample data shown below (order doesn't
matter, don't need the items without the dash). i.e., I need a distinct
list of the highest revision number for each item.
ITEM01-RV04
ITEM02-RV04
ITEM03-RV06
SQL to create sample data:
CREATE TABLE
qtemp/temp
(ITM NUMERIC (8 , 0) NOT NULL WITH DEFAULT, LITM CHAR (25 ) NOT NULL WITH
DEFAULT);
Insert into
qtemp/temp (ITM , LITM)
Values
(1 ,'ITEM01 ')
, (2 ,'ITEM01-RV01')
, (3 ,'ITEM01-RV02')
, (5 ,'ITEM01-RV03')
, (6 ,'ITEM01-RV04')
, (7 ,'ITEM02 ')
, (8 ,'ITEM02-RV02')
, (9 ,'ITEM02-RV03')
, (10,'ITEM02-RV04')
, (11,'ITEM03 ')
, (12,'ITEM03-RV05')
, (13,'ITEM03-RV06')
, (14,'ITEM04');
Regards,
Stumped in Austin
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
