How to use result of SELECT in a limit

Question

I have a table here that keeps repeating results over environments.

Cf :

Select * from SYSCOLUMNS where table_name = '*EXAMPLE*'
 
RESULT 1 | RESULT B 1 | ENV 1
RESULT 2 | RESULT B 2 | ENV 1
RESULT 3 | RESULT B 3 | ENV 1
RESULT 1 | RESULT B 1 | ENV 2 
RESULT 2 | RESULT B 2 | ENV 2

... etc etc.

I tried to use a group by but as there are slight changes in the encoding, it's not really effective. I tried to specify an environment too but from a machine to another, it changes. But I do have a Column named " Ordinal_Position " that gives a sort ID and that repeats too.

What I'd like to do is use it as a Single Value in my limit. here I show you my query :

SELECT SYSTEM_COLUMN_NAME, DATA_TYPE, STORAGE, COLUMN_TEXT, COLUMN_NAME, COLUMN_HEADING
FROM SYSCOLUMNS
WHERE TABLE_NAME = '*EXAMPLE*'
LIMIT (SELECT ORDINAL_POSITION
        FROM SYSCOLUMNS
        WHERE TABLE_NAME = '*EXAMPLE*'
        GROUP BY ORDINAL_POSITION
        ORDER BY ORDINAL_POSITION DESC LIMIT 1)

The limit instruction asks me a "Single Value" in order to work. isn't it possible to create a variable or something like that ? I tried to work with "AS" and "WITH" but I'm not getting any results.

PS : I'm in DB2, in an Iseries environment, and Syscolumns is an generated automatically by the system

Answer 1

Are you trying to get column information for the table from first available schema (environment)? Try this

select  SYSTEM_COLUMN_NAME, DATA_TYPE, 
        STORAGE, COLUMN_TEXT, COLUMN_NAME, COLUMN_HEADING
from
(
        select   SYSTEM_COLUMN_NAME, DATA_TYPE, 
        STORAGE, COLUMN_TEXT, COLUMN_NAME, COLUMN_HEADING ,
        rank() over   ( order by  table_schema) as rank1
        from  qsys2.syscolumns  
        WHERE TABLE_NAME = '*EXAMPLE*'
 ) a
 where rank1 = 1

To use result of select to limit rows, you can try this

select SYSTEM_COLUMN_NAME, DATA_TYPE, 
      STORAGE, COLUMN_TEXT, COLUMN_NAME, COLUMN_HEADING
from
(
    select SYSTEM_COLUMN_NAME, DATA_TYPE, 
    STORAGE, COLUMN_TEXT, COLUMN_NAME, COLUMN_HEADING ,
     row_number()  over( order by table_schema) as rn1
    from  qsys2.syscolumns  
    WHERE TABLE_NAME = '*EXAMPLE*'
) a
where rn1 <= (SELECT MAX(ORDINAL_POSITION)
        FROM SYSCOLUMNS
        WHERE TABLE_NAME = '*EXAMPLE*'
)