how to select a table that has most columns using syscat?

[d0m_at0m]

hello,

how to select a table that has a highest amount of columns (compared
to other tables) using syscat?

thanks in advance

[Ian]

d0m_at0m wrote:

hello,

how to select a table that has a highest amount of columns (compared
to other tables) using syscat?

I can't imagine how this would be useful, but:

select tabschema, tabname, count(*) as num_cols
from syscat.columns
group by tabschema, tabname
order by 3 desc
fetch first 1 row only;

[situ]

On May 23, 4:30 am, d0m_at0m <[e-mail]:

hello,

how to select a table that has a highest amount of columns (compared
to other tables) using syscat?

thanks in advance

even this may work i think ..

select * from syscat.tables
where colcount=(select max(colcount) from syscat.tables)

[situ]

On May 23, 4:54 am, Ian <[e-mail]:

d0m_at0m wrote:

hello,

how to select a table that has a highest amount of columns (compared
to other tables) using syscat?

I can't imagine how this would be useful, but:

select tabschema, tabname, count(*) as num_cols
from syscat.columns
group by tabschema, tabname
order by 3 desc
fetch first 1 row only;

even it may work i think ..

select * from syscat.tables
where colcount=(select max(colcount) from syscat.tables)

[Knut Stolze]

situ wrote:

On May 23, 4:54 am, Ian <[e-mail]:

>d0m_at0m wrote:

hello,

how to select a table that has a highest amount of columns (compared
to other tables) using syscat?

I can't imagine how this would be useful, but:

select tabschema, tabname, count(*) as num_cols
from syscat.columns
group by tabschema, tabname
order by 3 desc
fetch first 1 row only;

even it may work i think ..

select * from syscat.tables
where colcount=(select max(colcount) from syscat.tables)

SELECT *
FROM syscat.tables
ORDER BY colcount DESC
FETCH FIRST 1 ROW ONLY

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany