Menu
I'm trying to add a new column to an existing table, where the value is the row number/rank. I need a way to generate the row number/rank value, and I also need to limit the rows affected--in this case, the presence of a substring within a string.
Right now I have:
UPDATE table
SET row_id=ROW_NUMBER() OVER (ORDER BY col1 desc) FROM table
WHERE CHARINDEX('2009',col2) > 0
And I get this error:
Windowed functions can only appear in the SELECT or ORDER BY clauses.
(Same error for RANK())
Is there any way to create/update a column with the ROW_NUMBER() function? FYI, this is meant to replace an incorrect, already-existing "rank" column.
762
The PARTITION BY clause divides the result set into partitions (another term for groups of rows). The ROW_NUMBER() function is applied to each partition separately and reinitialized the row number for each partition. The PARTITION BY clause is optional.
If you'd like to number each row in a result set, SQL provides the ROW_NUMBER() function. This function is used in a SELECT clause with other columns. After the ROW_NUMBER() clause, we call the OVER() function. If you pass in any arguments to OVER , the numbering of rows will not be sorted according to any column.
The SQL ROW_NUMBER function is available from SQL Server 2005 and later versions. ROW_NUMBER adds a unique incrementing number to the results grid. The order, in which the row numbers are applied, is determined by the ORDER BY expression.
ROWID is a pseudocolumn that uniquely defines a single row in a database table. The term pseudocolumn is used because you can refer to ROWID in the WHERE clauses of a query as you would refer to a column stored in your database; the difference is you cannot insert, update, or delete ROWID values.
You can do this with a CTE, something like:
with cte as
(
select *
, new_row_id=ROW_NUMBER() OVER (ORDER BY col1 desc)
from MyTable
where charindex('2009',col2) > 0
)
update cte
set row_id = new_row_id
SQL Fiddle with demo.
78
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
