newbie q: how to select subtable and keyed data

[r***@chrysler.com]

This seems like it should be easy,
but finding acceptable syntax has proven very difficult.

Given a table with multicolumn data,
and a multicolumn hierarchical index,
how can a "subtable" be selected that contains the
multicolumn data and a multicolumn subset of the index?

Assume a table with a data column and 3 column index.

create table lookup
(val char(1), maj integer, min integer, rel integer);
create unique index lookup_i1 on lookup
(maj desc,min desc,rel desc)
insert into lookup values
('c',3,3,2)
,('z',3,3,1)
,('o',3,2,3)
,('y',3,2,2)
,('r',3,1,1)
,('r',2,4,2)
,('e',2,3,4)
,('x',2,3,3)
,('w',2,3,2)
,('v',2,3,1)
,('c',2,2,1)
,('t',2,1,1)
,('l',1,2,1)
,('y',1,1,5)
,('u',1,1,2)
,('t',1,1,0)
;

Try to populate a table with a data column and 2 column index,
where the data is copied from the first row in the larger
table with the same first part of the index.

It would seem this should be easy to do with "group by"
and "max()" but no success so far.

create table result
(val char(1), maj integer, min integer);
create unique index result_i1 on result
(maj desc,min desc)

The goal is to find a subselect

select ... lookup ...

such that the contents of result are as follows.
insert into lookup values
('c',3,3,2)
-- ,('z',3,3,1) -- superceded by rel=2 for maj,min=3,3
,('o',3,2,3)
-- ,('y',3,2,2) -- both superceded by rel=3
-- ,('r',3,1,1)
,('r',2,4,2)
,('e',2,3,4)
-- ,('x',2,3,3) -- first row only for maj,min = 2,3
-- ,('w',2,3,2)
-- ,('v',2,3,1)
,('c',2,2,1)
,('t',2,1,1)
,('l',1,2,1)
,('y',1,1,5)
-- ,('u',1,1,2) -- superceded by first in 1,1 sublist
-- ,('t',1,1,0)
;

Problems so far include:

distinct seems to preclude carrying the columns (val)
that are not part of the unique index up the select
nesting.

join includes ALL the rows for each rel
aggregate functions and/or where operate on all the rows
not just within the group.

What am I missing?
TIA

[r***@chrysler.com]

As seems to happen embarrassingly often,
after days of frustration,
a solution is found only scant minutes after posting a queestion.



To select data from a multi-column index table,
indexed on a subset of the index colums,
chain join and group expressions.

-- Create a table with a value and three column index
create table lookup (
value char(1)
,major integer
,minor integer
,rel integer
);
create unique index lookup1 on lookup (
major desc
,minor desc
,rel desc
);
-- Populate the table (sorted as indicated above)
insert into lookup values
('c',3,3,2),
('z',3,3,1),
('o',3,2,3),
('y',3,2,2),
('r',3,1,1),
('r',2,4,2),
('e',2,3,4),
('x',2,3,3),
('w',2,3,2),
('v',2,3,1),
('c',2,2,1),
('t',2,1,1),
('l',1,2,1),
('y',1,1,5),
('u',1,1,2),
('t',1,1,0);

-- Report the full table contents for comparison
select * from lookup order by major desc,minor desc, rel desc;

-- Create a subselectable table that picks the latest rel value for each major,minor
select * from ( -- validate subselect
select l.major,l.minor,l.value -- describe subtable
from (
select major,minor,max(rel) as latest -- collapse column must be last
from lookup
group by major, minor -- identify columns to keep
) as k
join -- lookup in table
lookup as l
on k.major=l.major and k.minor=l.minor and k.latest=l.rel
) as z
order by major desc,minor desc; -- order for easy validation

3 3 c
3 2 o
3 1 r
2 4 r
2 3 e
2 2 c
2 1 t
1 2 l
1 1 y

-- Create a subselectable table that picks the latest minor,rel value for each major
select * from ( -- validate subselect
select m.major,m.minor,r.rel,l.value -- display columns of interest
from (
select major,max(minor) as minor -- collapse on major index components first
from lookup
group by major -- identify major index resolution
) as m join ( -- lookup minor index components
select major,minor,max(rel) as rel -- collapse on minor index components next
from lookup
group by major,minor -- identify minor index resolution
) as r
on m.major=r.major and m.minor=r.minor join -- lookup target in table
lookup as l
on m.major=l.major and m.minor=l.minor and r.rel=l.rel
) as z
order by major,minor,rel -- order for easy validation
;

1 2 1 l
2 4 2 r
3 3 2 c




Please correct me if any of the following requirements can be relaxed.
Requirements:
1. The columns named in the group clause must precede
the aggregate functions in the corresponding select clause.
2. The aggregation must be nested in a select clause
separate from the value selection, rather than one select clause.
3. The on conditions must immediately follow their respective join
expressions in the chain.

Reporting,