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

Question

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

which of the 4x Sybase RDBMS products (ASE? IQ? SQLAnywhere? Advantage?) and version? SQLAnywhere (and IQ) tend to have more features (eg, windows functions) than ASE — markp-fuso, Jun 06 '22 at 13:39
Sybase (now SAP) `ASE` does not have support for windows functions (eg, `row_number()`, `over()`) — markp-fuso, Jun 08 '22 at 20:32
consider reviewing [How to create a minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) and then come back and update the question; in particular ... DDL for a table, `insert` statements to populate the table, and the expected result set (corresonding to the provided sample `insert` statements) — markp-fuso, Jun 08 '22 at 20:34

upizs · Answer 1 · 2022-06-09T04:59:24.127

1

You can create a new temp table with Identity (call it row number) that will enumerate the rows as IDs and then return that table.

SELECT rownumber=identity(10), name
  INTO #temp
  FROM table

SELECT * FROM #temp

You can experiment with the value passed into Identity as I don't know what it does.

edited Jun 09 '22 at 04:59

answered Jun 06 '22 at 04:55

upizs

89
8

i already tried also this: over (order by (select 1)), over (order by name) - same error – yndingo Jun 06 '22 at 05:20
i cant use over - it always says incorrect syntax, maybe i have old sybase driver – yndingo Jun 06 '22 at 05:22
@yndingo My apologies, I must have skipped the part where you already tried that. Have you tried NUMBER(*) like this? SELECT NUMBER( * ), name FROM table https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbreference/number-function.html here is more documentation. – upizs Jun 06 '22 at 05:42
Thank you, cool function. Unfortunately it also doent work. Says incorrect syntax near'\*'. SELECT NUMBER( * ), name.... If i remove number(*) all ok. – yndingo Jun 06 '22 at 06:11
Do you have any other tables joined in that query? In that case you would need to specify like table.* Maybe – upizs Jun 06 '22 at 11:49
I noticed here that you need to specify Ascending or descending in OVER parameter as such. OVER(ORDER BY name ASC) see if that helps? https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbusage/ug-olap-s-51258147.html – upizs Jun 06 '22 at 12:01
I have also seen NUMBER() used without parameter. It would also help to find out what version of Sybase you have. – upizs Jun 06 '22 at 12:28
Sybase Ase 16.0 – yndingo Jun 08 '22 at 15:21
https://stackoverflow.com/questions/21633696/finding-the-n-th-row-in-sybase-ase You can use something similar to the solution here. Create a temp table with Identity(8) that will enumerate the rows as id's but you can call row_number. I will edit my answer accordingly – upizs Jun 09 '22 at 04:53

Kazi Mohammad Ali Nur Romel · Answer 2 · 2022-06-08T15:48:36.157

1

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

Select * from
(
    select (select count(*)+1 from table a where t.idName >a.idName ) row_number, name
    from table t  
)x order by row_number

edited Jun 08 '22 at 15:48

answered Jun 06 '22 at 05:45

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

i have idName as unique. When i use it row_number unfortunately counts from zero. When i use name + id row_number shown are too big, when i use order by row_number in name+idName query is too heavy. It runs 1400sec and doesnt stop – yndingo Jun 06 '22 at 07:03
Re "counts from zero" - can't you just add 1 to it then? – Arvo Jun 06 '22 at 12:28
@yndingo I have revised the query to solve the issue. – Kazi Mohammad Ali Nur Romel Jun 06 '22 at 17:17
1. if i call only one field it runs fast. 2. problem begins when calling 2 fields, row_number is not ordered. 3. when call order by row_number script i stopped script after 5 minutes. I cant wait so long 4. i have voted for your answer but unfornately i cant use it – yndingo Jun 08 '22 at 04:08
extremely sorry for that. It would be great if you can share some sample data for better understanding. @yndingo I have added an order by clause. Please check now. – Kazi Mohammad Ali Nur Romel Jun 08 '22 at 09:25
i need to order by row_number, so i used it and as i said i cant wait so long. I cant share data sorry. – yndingo Jun 08 '22 at 15:20
Got your point @yndingo. Please try my revised answer to get the result in your desired order. – Kazi Mohammad Ali Nur Romel Jun 08 '22 at 15:49
select * from (select (select count(*)+1 from table a where t.idName>a.idName) as rownum, idName, Name from table t) x order by rownum. Stopped script after 300sec. Without ordering it runs about 5 sec – yndingo Jun 09 '22 at 04:18
It will be `order by row_number` not `order by rownum` – Kazi Mohammad Ali Nur Romel Jun 09 '22 at 05:20

score 1 · Answer 3 · answered Jun 09 '22 at 10:42

ah. Old question again... First, ASE didn't suppor the window function like IQ -- eg. this row_number() over ().. But you can get the similar effect using another ASE's function -- identity(n) Here's a simple Sample --

1> create table #t1(str varchar(5))
2> go
1> insert #t1 values('haha')
2> insert #t1 values('hehe')
3> insert #t1 values('hoho')
4> go
1> select * from #t1
2> go
 str
 -----
 haha
 hehe
 hoho
1> select rownumber=identity(10),str into #t2 from #t1
2> go
(3 rows affected)
1> select * from #t2
2> go
 rownumber     str
 ------------- -----
             1 haha
             2 hehe
             3 hoho

(3 rows affected)

Pls be noted -- this function -- identity(n) -- can only be used in "select into" statement, so you need to use it and put the resultset to a temp table then retrieve it with the generated rownumber. Hope it help.

score 0 · Answer 4 · answered Jun 06 '22 at 04:17

0

select COUNT(*) as number_row, name
from table

COUNT() is also return number of row Try This

answered Jun 06 '22 at 04:17

Uttam Nath

644
4
16

number_row returns 6046969 and doesnt change... I dont need total rows, just row#1, #2... – yndingo Jun 06 '22 at 04:33
if i use count + distinct + group -> number_row is 1 – yndingo Jun 06 '22 at 04:52

score 0 · Answer 5 · answered Jun 06 '22 at 05:14

0

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

answered Jun 06 '22 at 05:14

Uttam Nath

644
4
16

i cant use over - it always says incorrect syntax, maybe i have old sybase driver – yndingo Jun 06 '22 at 05:22

score 0 · Answer 6 · answered Jun 08 '22 at 03:58

0

realized by temporary table: alter table #tempTable add id numeric(10,0) identity not null

answered Jun 08 '22 at 03:58

yndingo

25
1
7

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

6 Answers6