Dynamically select TOP N rows from table

Database
Enthusiast

Dynamically select TOP N rows from table

Hi All,

I need to perform dynamically select TOP N row from table.

SELECT TOP (10-4) COLUMNNAME

FROM DBC.COLUMNS

WHERE DATABASENAME='DATABASE_NAME'

AND TABLENAME ='TABLE_NAME';

This is because I want create 1 macro in which series of DML statements are there.

DATABASE_NAME and TABLE_NAME are parameterized.

And this activity needs to be done for multiple table. But number of fields are changing in each table.

Last 4 columns are HISTORY columns ,means these maintain record effectivity. So I want to skip them.

Is there any other way to perform this activity?

Thanks in advance...!!

2 REPLIES
Senior Apprentice

Re: Dynamically select TOP N rows from table

- use QUALIFY ROW_NUMBER instead of TOP

- use dbc.ColumnsV instead of dbc.Columns

- without ORDER BY the result is random

If it's always all but the last four columns:

SELECT  COLUMNNAME
FROM DBC.ColumnsV
WHERE DATABASENAME='DATABASE_NAME'
AND TABLENAME ='TABLE_NAME'
QUALIFY ROW_NUMBER () OVER (ORDER BY ColumnID) <= COUNT(*) OVER () -4
Enthusiast

Re: Dynamically select TOP N rows from table

Thanks for helping to resolve the issue...!! :)