Re: Try to figure out how to write a query which will show ONLY the highest revision number for a given Item number

El Wed, 3 Feb 2016 19:07:20 -0600
"Steve Landess" <sjl_abc@xxxxxxxxxxx> escribió:

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

I tried this SQLs (STRSQL) and it worked:

> CREATE TABLE qtemp/temp (ITM NUMERIC (8 , 0) NOT NULL WITH DEFAULT,
LITM CHAR (25 ) NOT NULL WITH DEFAULT)
Table TEMP created in QTEMP.
> 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')
13 rows inserted in TEMP in QTEMP.

> with A as (
select LITM,
row_number() over (partition by substr(LITM,1,6)
order by LITM desc) N
from TEMP
where LITM like '%-%'
)
select LITM from A where N=1

...and here's the result:


ITEM01-RV04
ITEM02-RV04
ITEM03-RV06

ITEM04 does not appear because there are no records with dashes.