Forum Discussion
Memorable MemberTIP::COPY DATA::SOURCE::TSQL QUERY::LIMITATION
Hello Element115 ,
Thank you for your update and for clarifying your progress.
You’re absolutely right in this case, PARTITION BY NULL isn’t strictly necessary. I included it to intentionally ensure ROW_NUMBER() treats the whole dataset as one partition, keeping the ID sequence continuous. It’s something we often do as a best practice to make the partitioning behavior clear.That said, leaving it out gives you the same result since the query defaults to one partition anyway. So, feel free to skip it, it won’t affect how the query runs.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you
Hello Element115 ,
The ROW_NUMBER() function generates unique row numbers, and PARTITION BY groups the data into sections where numbering restarts. To partition by year and month, you should write PARTITION BY YEAR(vis.[DATE]), MONTH(vis.[DATE]) . This creates a new row number sequence for each year-month combination. The ORDER BY vis.[DATE] DESC ensures the latest dates get the first numbers
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Memorable MemberAnonymous Unfortunately, your SQL statement does not work. You can't reference another alias from within the same SELECT clause. The SELECT clause needs to execute and complete firts, then the aliases become available. I just ran it and it fails with a syntax error.
But I get the point. You could use it within a CTE, and then refer to the alias in the ROW_NUMBER() outside the CTE.
A nd you need to convert the types DATE and TIME to varchar explicitly as the '+' operator is not overloaded.
Super UserHi Element115 Just as you mentioned you can use the cast function and all the required columns inside a cte which will act as a composite key which then you can select and user ROW_NUMBER and order by the key I think this could work