All Forums Database
Priti_Kanal 8 posts Joined 05/16
20 Jun 2016
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...!!
 
 
 
 

dnoeth 4628 posts Joined 11/04
20 Jun 2016

- 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

 

Dieter

Priti_Kanal 8 posts Joined 05/16
20 Jun 2016

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

You must sign in to leave a comment.