cancel
Showing results for 
Search instead for 
Did you mean: 

row_number with out order by in hana

0 Kudos

Hi,

I have a calculation view which exports my products in a specific order. I want to retain the order of products. But when i expose it as a OData service , the products are sorted by product key. While it is built in feature / Bug of OData that it sorts the records by key if no order by is mentioned , i want to add an extra column in my view to retain the order . For that , I thought of storing the row number for each row. I dont have any columns which i have to mention iin order by clause. I could not use row_number function without order by clause in hana. When i write below statement,

 

select

PRERULE, POSTRULE , row_number() over(order by (SELECT 1)) as rownum from "PAL"."AP_RULES_P" WHERE "PRERULE" = '73'

I got an error saying

 

 

incorrect syntax near ")": line 1 col 66 (at pos 66)

Can some one help me how to fix the situation?

Cant we use row_number with out order by in hana?

regards,
Deepthi lakshmi.A

Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

Hello Deepthi,

Have you tried the following code instead of SELECT 1 ... do

SELECT 1 FROM DUMMY

Regards, Werner

Werner Steyn

Platinum Product Expert - Database and Technology (D&T) | SAP HANA

Customer Solution Adoption (CSA) | Technology and Innovation Platform (TIP)

i033659
Advisor
Advisor
0 Kudos

Hi Werner,

Is there meanwhile a more elegant solution? I need it for a view resp. in a select statement. Therefore I think the AS IDENTITY declaration cannot really be used.

Thanks,

Ingo

lbreddemann
Active Contributor
0 Kudos

It's possible to use ROW_NUMBER() OVER().

But since this really only adds an increasing number to every line of the result set, there is no guarantee that the same line will receive the same number when the statement is executed again.

It's another variant of "without ORDER BY no sorting must be assumed".