Search code examples
sqlrow-numberpartition-by

Row_Number over (partition by...) all columns


I have a view with something like 150 columns and I want to add an Id column to that view. Is it possible not to write all the column names in the over (partition by... ) statment?

something like this:

row_number over (partition by *) As ID?

Solution

  • If you want to add a row number to the view, don't you just want an order by with no partition?

    If so, you can use one of the following, depending on the database:

    select row_number() over ()
    select row_number() over (order by NULL)
    select row_number() over (order by (select NULL))
    

    Your approach would be enumerating identical rows, not providing a row number over all rows.