Using Row_number() OVER(partition BY..) along with declaring local variables

Question

I have a sql query, in this sql query I want to select distinct columns irrespective of column first. For other sql query I use Row_number() OVER(partition BY..) and I also need to use inner join. The query in which I want to use row_number and inner join is -

DECLARE @columns NVARCHAR(MAX)

DECLARE @params NVARCHAR(MAX) = '@columns NVARCHAR(MAX) OUTPUT'

DECLARE @sql NVARCHAR(MAX) = 'SELECT @columns = STUFF(
(
    SELECT '',''+ [column_name] FROM information_schema.columns
    WHERE (table_schema = ''dbo''
          AND table_name = ''main_mps_dqs_analog'') 
          AND (ordinal_position <= 73) FOR XML PATH('''')),1,1,'''')'

EXEC sp_executesql @sql, @params, @columns OUTPUT

SET @sql = 'SELECT '+ @columns + ' FROM dbo.main_mps_dqs_analog WHERE logtime BETWEEN ''2014-10-10 07:17:00'' AND ''2014-10-10 08:47:00''' 

EXEC(@sql)

I want to apply inner join of this table with INDUS2_BDS.dbo.ddtable and I want beam_current and logtime of this INDUS2 database table and how to apply partition BY beam_current in this query.

you want to add a inner join and a row_number to your dynamic sql? But you want to PARTITION BY what column? You are not supplying enough information to get actual help... PRINT @sql instead of the execute to see the query you are trying to execute and Google ROW_NUMBER — mxix

tiddi rastogi tiddi rastogi · Accepted Answer · 2015-03-17T09:15:08

SET @sql = 'SELECT ' + @columns + ' ,AnotherTable.beam_current, RowNumber() Over(Partition By SomeColumn Order By SomeColumn) AS Rn
 FROM dbo.TableName join AnotherTable on TableName.SomeColumn = AnotherTable.SomeColumn
WHERE logtime BETWEEN ''2014-10-10 07:17:00'' AND ''2014-10-10 08:47:00'''

I solved it by this sql query with the help from Giorgi

Using Row_number() OVER(partition BY..) along with declaring local variables

1 Answers