How to use result of SELECT in a limit

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

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*'
)