In Oracle SQL, I want to create a view.

In Column 1, it should have a distinct list of chars.

select distinct *name* from view1
--> name1
    name2
    name3
    name4...

Now in the second column, there should be numbers, which depend on this number:

select max(number) number_max from view2
--> 17

Now I want the second column to be ascending, depending on that number. The list of numbers for each name. The table should look like:

name1 18  ->(number_max 1)
name1 19  ->(number_max 2)
name1 20
name2 18
name2 19
name2 20
name3 18
....

How do I do this? Thank you so much!

CodePudding user response：

This is how I understood it:

Sample data (your views, view1 and view2, simplified):

SQL> with
  2  view1 (name) as
  3    (select 'name1' from dual union all
  4     select 'name1' from dual union all
  5     select 'name1' from dual union all
  6     select 'name2' from dual union all
  7     select 'name3' from dual union all
  8     select 'name3' from dual
  9    ),
 10  view2 (c_number) as
 11    (select 15 from dual union all
 12     select 17 from dual
 13    )
 14  --

Query begins here; subquery that is cross-joined to view1 just fetches the maximum number column value which is then added to result of the row_number analytic function that partitions data per each name:

 15  select
 16    a.name as col1,
 17    row_number() over (partition by a.name order by null)   b.number_max as col2
 18  from view1 a cross join (select max(c_number) number_max from view2) b
 19  order by 1, 2;

COL1        COL2
----- ----------
name1         18
name1         19
name1         20
name2         18
name3         18
name3         19

6 rows selected.

SQL>

CodePudding user response：

You can use GROUP BY name to find the DISTINCT names and find the maximum maximum by nesting an MAX aggregation function inside a MAX analytic function and then CROSS JOIN to a row-generator.

For example, if you wanted 3 rows for each name:

SELECT m.name,
       m.number_max   l.value AS value
FROM   ( SELECT name,
                MAX(MAX("NUMBER")) OVER () AS number_max
         FROM   table_name
         GROUP BY name
       ) m
       CROSS JOIN (
         SELECT LEVEL AS value FROM DUAL CONNECT BY LEVEL <= 3
       ) l;

Which, if you have the sample data:

CREATE TABLE table_name (name, "NUMBER") AS
SELECT 'name1', 17 FROM DUAL UNION ALL
SELECT 'name1',  1 FROM DUAL UNION ALL
SELECT 'name1',  7 FROM DUAL UNION ALL
SELECT 'name2', 15 FROM DUAL UNION ALL
SELECT 'name2', 15 FROM DUAL UNION ALL
SELECT 'name3',  5 FROM DUAL UNION ALL
SELECT 'name3',  7 FROM DUAL UNION ALL
SELECT 'name4',  2 FROM DUAL UNION ALL
SELECT 'name4',  1 FROM DUAL;

Outputs:

NAME	VALUE
name1	18
name2	18
name3	18
name4	18
name1	19
name2	19
name3	19
name4	19
name1	20
name2	20
name3	20
name4	20

db<>fiddle here