How to get ROW_NUMBER () in SQL?

Question

I tried to get the line number using ROW_NUMBER(), but it shows the following error:

cannot format message 13: 896 - message file C: \ WINDOWS \ firebird.msg not found. SQL dynamic error. SQL error code = -104. Unknown token - row 2, column 66.

Here is my code:

SELECT  avg(CSIDTL.RATING) ,SVD.SVCADVISORNAME, ROW_NUMBER() OVER(ORDER BY avg(CSIDTL.RATING) )
        FROM T_APPT_BOOKING_MSTR MSTR ,T_APPT_CSI_SURVEY CSI,T_APPT_CSI_SURVEY_DTL CSIDTL,
        T_SVC_SVCADVISOR_MASTER SVD
        WHERE MSTR.APPTBKID = CSI.APPTBKID
        AND CSI.CSI_SURVERYID = CSIDTL.CSI_SURVERYID
        AND SVD.SVCADVISORID = MSTR.SVCADVISORID
        AND CSI.FEEDBACK_STATUS = 'Y'
        AND CSIDTL.question ='Service Advisor'
        GROUP BY SVD.SVCADVISORNAME
        ORDER by avg(CSIDTL.RATING)

Answer 1

The feature ROW_NUMBER()was introduced with Firebird 3.0, released just a few days ago. See Release Notes, chapter Window Functions (Analytics) for the exact syntax. The error you get suggests that you are using an older version of Firebird that does not have this feature.

Answer 2

Firebird 2.5

: http://www.firebirdfaq.org/faq343/

SELECT rdb$get_context('USER_TRANSACTION', 'row#') as row_number, DUMMY, A.*
FROM your_table A
CROSS JOIN
(SELECT rdb$set_context('USER_TRANSACTION', 'row#',
COALESCE(CAST(rdb$get_context('USER_TRANSACTION', 'row#') AS INTEGER), 0) + 1) AS dummy
FROM rdb$database) dummy

Answer 3

Another alternative in Firebird 2.5 is to use generators

CREATE GENERATOR tmp$rn;
UPDATE my_table t SET t.id_field = (SELECT FIRST 1 NEXT VALUE FOR tmp$rn AS "row_number"
FROM my_table ORDER BY another_field1 DESC, another_field2 DESC);
DROP GENERATOR tmp$rn;