How to fetch below output using SQL?-CodePudding

Table

output:

How to fetch the output when I pass ID from cycleTable to get the cyc_id and then pass cyc_id to LineTable to get LN_ID. I want to get all the LN_ID for the available LN_NO with biggest cyc_id.

CodePudding user response：

The following query produces the output you want.

SELECT LN_ID 
FROM table
WHERE CYC_ID =
           (SELECT CYC_ID
           FROM table
           WHERE LN_ID = (SELECT MAX(LN_ID) FROM table))

CodePudding user response：

You explained it; what remains to be done is to translate words into several lines of code. This is one option:

Sample data:

SQL> with
  2  cycle_table (cyc_id, id) as
  3    (select 1002001, 1000000 from dual union all
  4     select 1002002, 1000000 from dual union all
  5     select 1002003, 1000001 from dual union all
  6     select 1002004, 1000001 from dual
  7    ),
  8  line_table (ln_id, cyc_id, ln_no) as
  9    (select 1002009, 1002004, 1 from dual union all
 10     select 1002004, 1002002, 1 from dual union all
 11     select 1002006, 1002003, 1 from dual union all
 12     select 1002005, 1002002, 1 from dual union all
 13     select 1002002, 1002001, 1 from dual union all
 14     select 1002010, 1002004, 1 from dual union all
 15     select 1002007, 1002003, 2 from dual union all
 16     select 1002034, 1002002, 2 from dual union all
 17     select 1002033, 1002002, 2 from dual union all
 18     select 1002003, 1002001, 2 from dual union all
 19     select 1002012, 1002004, 2 from dual union all
 20     select 1002011, 1002004, 2 from dual union all
 21     select 1002013, 1002004, 3 from dual union all
 22     select 1002014, 1002004, 3 from dual union all
 23     select 1002008, 1002003, 3 from dual
 24    )

Query you might be interested in:

 25  --
 26  select l.ln_id
 27  from line_table l
 28  where l.cyc_id in (select max(c.cyc_id)
 29                     from cycle_table c
 30                     where c.id = &par_id
 31                    );
Enter value for par_id: 1000000

     LN_ID
----------
   1002004
   1002005
   1002034
   1002033

SQL> /
Enter value for par_id: 1000001

     LN_ID
----------
   1002009
   1002010
   1002012
   1002011
   1002013
   1002014

6 rows selected.

SQL>