row number to result set of a SQL query (row_number doesn't work)-CodePudding

using dbeaver some custom sybase driver. row_number doesn't work:

incorrect syntax near 'over'

select row_number() over(), name
from table

tried also this

over (order by (select 1))
over (order by name)

tried variables:

select statement that assigns a value must not be combined with data-retrieval operations

declare @num int
set @num = 1
select name, @num = @num   1
from table
cross join (select @num = 1)

Thinking about temporary table with primary int key

CodePudding user response：

select COUNT(*) as number_row, name
from table

COUNT() is also return number of row Try This

CodePudding user response：

Your OVER() needs an ORDER BY parameter like this:

SELECT row_number() OVER(ORDER BY name), name
FROM table

probably best to order by id, but it's up to you.

CodePudding user response：

select ROW_NUMBER() OVER(ORDER BY name) as RowNum, name
from table

OVER() in that specify one column name and AS is use to set name of column and row column

Try this

CodePudding user response：

If name column has unique names then you can use subquery to get your row numbered as below:

select (select count(*) from table a where t.name>a.name) row_number, name
from table t